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