Marco to only commence from row 5

RichJW

Board Regular
Joined
Jul 7, 2015
Messages
94
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?

VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
try change to

Firstrow = .UsedRange.Cells(5).Row
 
Upvote 0
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".
 
Upvote 0
Try

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

'rest of code

Dave
 
Upvote 0
Or
Simply
VBA Code:
Firstrow = .Cells(1).Offset(4).Row
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top