Deleting rows below a certain point (not fixed)

[PE New User]

Hi,

I am trying to remove all data below the last entry for a specified product (I actually have to find the last row of data by finding the following product in the list in order to insert the row where I need it) - I have successfully inserted a row to locate the last entry, but am struggling to write the instruction to select all data below this break and delete it.

The script I have so far is:

Worksheets("DATA SHEET").Columns(8).Find("Electricity NHH").Select
ActiveCell.EntireRow.Insert

Can anyone help?

 

[farmerscott]

Untested and off the top of my head I would use something like-

rownumber= Worksheets("DATA SHEET").Columns(8).Find("Electricity NHH")

then

Range("H"&rownumber":H"&lastrow).clearcontents

Hope that helps,

FarmerScott

 

[PE New User]

Tried using the above, but this didn't quite work so spoke with a colleague who suggested the following:

Sub MOPSweeps()


Dim rngH As Range


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
' Remove rows that do not contain Electricity HH
    
    Worksheets("DATA SHEET").Activate


    rngH = Range("H2:H").set
    
    x = 1
    
    For Each x In rngH
    If x <> "Electricity HH" Then Range("H" & x.Row).EntireRow.Delete
    Next x
        
End Sub

This doesn't quite work either - it gets stuck at the 'rngH = Range' point. Can anyone suggest why this doesn't work or help with an alternative?

 

[farmerscott]

Try on a copy of your work-

Sub MOPSweeps()

Dim lr as long
Dim rngH As Range
Dim x as long


    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    
' Remove rows that do not contain Electricity HH
    
    Worksheets("DATA SHEET").Activate

lr = Worksheets("DATA SHEET").Cells(Rows.Count, "H").End(xlUp).Row
    Set rngH = Range("H2:H"&lr)
    
  
    
    For x= lr to 2 step -1 'assumes a header row.
    If cells(x,8).value <> "Electricity HH" Then Range("H" & x.Row).EntireRow.Delete
    End if
    'when 'Electricity HH" is found as the last entry (as we are working from the bottom up) the code stops. 
     If cells(x,8).value= "Electricity HH" then exit sub
    end if 
    Next x
        
End Sub

 

[PE New User]

Have just pasted the above into the macro and it won't let me step through - apparently x within the range is an Invalid Qualifier???

If Cells(x, 8).Value <> "Electricity HH" Then Range("H" & x.Row).EntireRow.Delete

 

[farmerscott]

Try-

Sub MOPSweeps()
' Find last enrty of "Electricity HH" and delete rows below.

Dim lr As Long
Dim rngH As Range
Dim x As Long

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With
    

    
Worksheets("DATA SHEET").Activate
lr = Worksheets("DATA SHEET").Cells(Rows.Count, "H").End(xlUp).Row
Set rngH = Range("H2:H" & lr)
    
  
    
    For x = lr To 2 Step -1 'assumes a header row.
    If Cells(x, 8).Value <> "Electricity HH" Then
        Rows(x).EntireRow.Delete
    End If
    'when 'Electricity HH" is found as the last entry (as we are working from the bottom up) the code stops.
     If Cells(x, 8).Value = "Electricity HH" Then Exit Sub
    
    Next x
        
With Application
        .ScreenUpdating = True
        .EnableEvents = True
    End With
End Sub