How To Get Row Number After AutoFiltering - Excel VBA

[quakeworld]

Hi,

I'm trying to write code that would get the row numbers of non-continuous rows. So for example after applying the autofilter only rows 1, 5 and 7 show up.

I'm having difficulty getting the row number as i need to obtain the values in certain cells in that row.

can anyone help? thanks

 

[Jaydip]

Hi,

I'm trying to write code that would get the row numbers of non-continuous rows. So for example after applying the autofilter only rows 1, 5 and 7 show up.

I'm having difficulty getting the row number as i need to obtain the values in certain cells in that row.

can anyone help? thanks

You can look at this

Sub Test()
    Dim col As Range, rngtest As Range

    Set rngtest = Range("A2:A19")

    For Each col In rngtest.SpecialCells(xlCellTypeVisible)
        Debug.Print col 
    Next cl

End Sub

 

[Tweak155]

Hi,

I'm trying to write code that would get the row numbers of non-continuous rows. So for example after applying the autofilter only rows 1, 5 and 7 show up.

I'm having difficulty getting the row number as i need to obtain the values in certain cells in that row.

can anyone help? thanks

I'm confused by your description. Jaydip's code may work for you, but if not, it might help to show what you currently have.

Also if you specifically need the row numbers, just do col.Row in the code above.

 

[quakeworld]

You can look at this

Sub Test()
    Dim col As Range, rngtest As Range

    Set rngtest = Range("A2:A19")

    For Each col In rngtest.SpecialCells(xlCellTypeVisible)
        Debug.Print col 
    Next cl

End Sub

I'm getting a run-time error '424': object required error on this line:

For Each col In rngtest.SpecialCells(xlCellTypeVisible)

 

[quakeworld]

I'm confused by your description. Jaydip's code may work for you, but if not, it might help to show what you currently have.

Also if you specifically need the row numbers, just do col.Row in the code above.

sorry if my description is vague... say i have this:

column A
a1 1
a2 2
a3 3
a4 4
a5 5
a6 6
a7 7
a8 8
a9 9
a10 10

i do an autofilter in vba which results in this:

column A
a1 1
a5 5
a7 7

i'm trying to get the row# of a1, a5 and a7 (so i can assign them to a variable and do all that manipulation stuff)... i'm having difficulty because the row #s (a1, a5 and a7) are non continous
10

 

[quakeworld]

here's the actual sub i have:

Sub filterMasterList()

Dim myTable As Range

Dim col As Range, rngTest As Range

i = 1
Set myTable = wsMasterList.ListObjects(1).Range

wsMasterList.Range("A1").Select
Selection.AutoFilter

With wsUnique

Do While .Range("A" & i) <> ""

ActiveSheet.ListObjects("Table_ExternalData_1").Range.AutoFilter Field:=3, _
Criteria1:=.Range("A" & i).Value

numberofrows = myTable.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1 ' get number of rows after autofilter
Set rngTest = ("A2:A" & numberofrows)

For Each col In rngTest.SpecialCells(xlCellTypeVisible)
MsgBox (col)
Next col


i = i + 1

' MsgBox (wsMasterList.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select)


Loop

End With

End Sub

 

[quakeworld]

I'm getting a run-time error '424': object required error on this line:

For Each col In rngtest.SpecialCells(xlCellTypeVisible)

durr.. i forgot to include the worksheet object :

Set rngTest = wsMasterList.Range("A2:A" & numberofrows)

works now.. although it's giving me the column values..not the row #

 

[quakeworld]

found a workaround ... i'll just add a column to count from 1 to lastrow and use the values in the cells in the new column to keep track of the rows

 

[Tweak155]

found a workaround ... i'll just add a column to count from 1 to lastrow and use the values in the cells in the new column to keep track of the rows

You shouldn't need to. col.Row will give you the row.

Can you paste the latest sub?

 

[quakeworld]

You shouldn't need to. col.Row will give you the row.

Can you paste the latest sub?

here ya go:

Sub filterMasterList()

Dim myTable As Range

i = 1
x = 2
Set myTable = wsMasterList.ListObjects(1).Range

wsMasterList.Range("A1").Select
Selection.AutoFilter

With wsUnique

Do While .Range("A" & i) <> ""

wsMasterList.ListObjects("Table_ExternalData_1").Range.AutoFilter Field:=3, _
Criteria1:=.Range("A" & i).Value

numberofrows = myTable.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1 ' get number of rows after autofilter
wsMasterList.UsedRange.Offset(1, 0).SpecialCells(xlCellTypeVisible).Select
Selection.Copy
wsTemp.Range("A" & x).PasteSpecial (xlPasteValues)
x = wsTemp.Range("A65536").End(xlUp).Row + 1

i = i + 1

Loop

End With

End Sub


my idea of putting the row number in a new column didn't work either because when i did a count it it still counted the numbers in between the autofiltered items. for example:


autofiltered:

column A columnB
a1 1
a5 5
a10 10

doing a count within a loop gave me 1, 2 3 instead of 1, 5, 10.

so what i did was what i was trying to avoid in the first first place that is to copy the autofiltered table and paste it in a new sheet (wsTemp) so that way the row #s are sequential.

 

[Tweak155]

Holy cow your code is baffling me.

Let's do things in progressive steps:

1 - Add the filter headers
2 - Apply your filter to the entire set of data
3 - Select only the visible data in column A (using .SpecialCells(xlCellTypeVisible)
4 - This range now has the .Rows.Count property

Looking at your code, you may just be able to change .Count to Rows.Count, but it will act goofy if you don't select only a single column.

 

[Tweak155]

You know what, I've read through your posts and code several times now. Can we just start back from the beginning before you wrote any code... what is your goal and what do you have available to you?

You may just be complicating the entire process.