Autofilter issue: ignore non-visible rows

[EM1987]

Hi,
I'm genuinely stuck on this, and cannot find a solution so any help would be much appreciated!

Basically, I have a spreadsheet with over 20000 rows of data and have used autofilter to find the information I want to check. Once I have these rows I want to check whether the cells in Column AE are higher/lower than the number (in this case '3'). Once it finds a cell that meets this condition then it copies and pastes the whole row so that I can review the entry.

The problem I'm having is that it doesn't work all the time. I've tested it a number of times in break mode & played it throughout and it works as expected, whereas other times it picks up the top row (header) and duplicates it or it picks up hidden rows and pastes them in sheet 2 if the condition is met. I want it to just pick up rows if they are visible (using the filter) if the condition is met.
Here's what I have so far:

[FONT=Calibri][SIZE=3][FONT=Calibri][SIZE=3] 
Sub Lvl3v2()
'Level 3 
Dim i As Integer
Dim r As Range
Dim c As Range
　
i = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set r = Worksheets("Sheet1").Range("AE3:AE" & i).SpecialCells(xlCellTypeVisible)
'filter on lvl 3
Worksheets("Sheet1").Range("A:AH").AutoFilter Field:=17, Criteria1:="LV3"
'if less than or greater than 3
For Each c In r
If c.Value <> 3 Then
c.EntireRow.Copy
Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
End If
Next c
End Sub
[/SIZE][/FONT][/SIZE][/FONT]

If anyone has any way to resolve this issue or can point me in the right direction I would really appreciate it!
　
Thanks in advance.

 

[XLfriend777]

Hi EM1987 - One thing you might consider is copying just the visible rows to a new sheet and then running your macro. To copy just the visible rows go to Find & Selec -> Go To Special ... -> select "Visible cells only". Then copy and paste them into a new sheet. Run your macro on the new sheet with only visible rows present.
If this works, you could incorporate it into the first part of your macro.
Hope this helps.</SPAN>

 

[EM1987]

Hi goesr,

Thanks for your response (and sorry about the delay of mine!). I have been able to find a way to do this by separating the autofilter into a different sub and calling it before the condition part of the original.

Please see code below:

Sub FLv3
[FONT=Calibri][SIZE=3]
'filter on lvl 3
Worksheets("Sheet1").Range("A:AH").AutoFilter Field:=17, Criteria1:="LV3"

End Sub[/SIZE][/FONT]

Then...

[FONT=Calibri][SIZE=3]Sub Lvl3v2()
'Level 3 
Dim i As Integer
Dim r As Range
Dim c As Range
　
i = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
Set r = Worksheets("Sheet1").Range("AE3:AE" & i).SpecialCells(xlCellTypeVisible)

'if less than or greater than 3
For Each c In r
If c.Value <> 3 Then
c.EntireRow.Copy
Worksheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues

End If

Next c

End Sub[/SIZE][/FONT][FONT=Calibri][FONT=Calibri]
[/FONT][/FONT]

I then 'called' both of the above subs in order to pull out visable rows which met the condition set above:

Sub BringItHome

Call FLv3
Call [FONT=Calibri][SIZE=3]Lvl3v2[/SIZE][/FONT]

End Sub

Hope this helps someone!!


Thanks,

EM1987