Marco to only commence from row 5

[RichJW]

Hi all,

I have the following macro, which deletes any row not equalling the word “Apple” in column D.

However, I wish it to only commence from row 5 of the data in the tab, instead of deleting the top 4 rows (which are headings) as it currently does. The end point is variable, so it could be row 10, or row 60.

Can anyone help with this, please?

Sub FilterForCRS()

Dim Firstrow As Long

Dim lastRow As Long

Dim Lrow As Long

Dim CalcMode As Long

Dim ViewMode As Long

With Application

CalcMode = .Calculation

.Calculation = xlCalculationManual

.ScreenUpdating = False

End With

With Sheets("CPSPG")

.Select

ViewMode = ActiveWindow.View

ActiveWindow.View = xlNormalView

.DisplayPageBreaks = False

Firstrow = .UsedRange.Cells(1).Row

lastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row

For Lrow = lastRow To Firstrow Step -1

With .Cells(Lrow, "D")

If Not IsError(.Value) Then

If .Value <> "APPLE" Then .EntireRow.Delete

End If

End With

Next Lrow

End With

ActiveWindow.View = ViewMode

With Application

.ScreenUpdating = True

.Calculation = CalcMode

End With

End Sub

Thanks,
Rich

 

[AlanY]

try change to

Firstrow = .UsedRange.Cells(5).Row

 

[RichJW]

Thanks @AlanY, I made that change, however it still removes the top 4 rows.
It was this line I edited - Firstrow = .UsedRange.Cells(1).Row - replacing the "1" with a "5".

 

[dmt32]

Try

FirstRow = 5
LastRow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
If LastRow < FirstRow Then Exit Sub

'rest of code

Dave

 

[mohadin]

Or
Simply

Firstrow = .Cells(1).Offset(4).Row

 

[AlanY]

Thanks @AlanY, I made that change, however it still removes the top 4 rows.
It was this line I edited - Firstrow = .UsedRange.Cells(1).Row - replacing the "1" with a "5".

it works when I test it

 

[RichJW]

Thanks all, greatly appreciate your help.
Both @dmt32 and @mohadin worked beautifully, thank you both.

Appreciate your advice @AlanY, but I guess something my end stopped it working. Hey ho, that's excel for ya!

Cheers all,
Rich