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:

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
try change to

Firstrow = .UsedRange.Cells(5).Row
 

RichJW

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

Well-known Member
Joined
Jul 3, 2012
Messages
6,490
Office Version
  1. 2019
Platform
  1. Windows
Try

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

'rest of code

Dave
 

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,091
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Or
Simply
VBA Code:
Firstrow = .Cells(1).Offset(4).Row
 

AlanY

Well-known Member
Joined
Oct 30, 2014
Messages
4,243
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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

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

Watch MrExcel Video

Forum statistics

Threads
1,128,127
Messages
5,628,860
Members
416,345
Latest member
sayad

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