Autofilter issue: ignore non-visible rows

EM1987

New Member
Joined
Dec 13, 2013
Messages
4
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:

Code:
[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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

goesr

Well-known Member
Joined
Nov 15, 2013
Messages
654
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

New Member
Joined
Dec 13, 2013
Messages
4
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:

Code:
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...

Code:
[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:

Code:
Sub BringItHome

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

End Sub


Hope this helps someone!! :)


Thanks,

EM1987
 

Watch MrExcel Video

Forum statistics

Threads
1,127,355
Messages
5,624,213
Members
416,017
Latest member
moritz210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top