How to Get the Addresses of Visible Rows from a Filtered Data in Excel using VBA
The following function allows you to get the Address of each visible rows from a filtered sets of data in Excel using VBA.
[VBA]
To use the above function, you can assigned the following macro to a button or shape.
And you can see the output in the Immediate window as shown below.
[VBA]
Dim FilteredRows as Variant Public Function GetFilteredRows(Optional ByVal RowPrefixed As Boolean) Dim Rng As Range, rngF As Range, rngVal As Range 'Ranges Dim val As Variant 'Range Value Dim i As Integer 'Counter Dim lRow as long 'Last Row Application.ScreenUpdating = False Sheets("Raw Data").Select lRow = WorksheetFunction.CountA(Range("A:A")) 'Set the range of all visible cells of the filtered data Set rngF = Range("A2", Cells(ActiveSheet.UsedRange.Rows.Count, _ Range("A2").Column)).SpecialCells(xlCellTypeVisible) For Each Rng In Range("$A2:$A$" & lRow) If Not Intersect(Rng, rngF) Is Nothing Then If rngVal Is Nothing Then Set rngVal = Rng Else Set rngVal = Union(rngVal, Rng) End If If rngVal.Cells.Count = lRow Then Exit For End If Next Rng 'Resize array variable ReDim FilteredRows(0 To Application.CountA(rngVal)) As Variant For Each val In rngVal If RowPrefixed = True Then FilteredRows(i) = val.Address Else FilteredRows(i) = Split(val.Address, "$")(2) End If Debug.Print val.Address & " - " & Split(val.Address, "$")(2) i = i + 1 Next val Debug.Print rngVal.Address Applicaiton.ScreenUpdating = True End Function
To use the above function, you can assigned the following macro to a button or shape.
Sub SetFilter() Call GetFilteredRows(True) End Sub
And you can see the output in the Immediate window as shown below.
Caesars Completes $4 Million in Sportsbook - Dr.MCD
ReplyDelete"For 서울특별 출장마사지 now, Caesars Entertainment is looking for 평택 출장안마 new 제천 출장안마 players to place bets, 사천 출장마사지 and they're ready for action," 여주 출장마사지 Caesars.