Hide all rows below specific text in cell

[Cooki]

I need to hide all rows under "Total" in column B

Got a macro to delete blank rows between the last cell with data and Total, but every time it deletes the hidden cells under total appear, very annoying

Sub HideRowsBelow()

Application.ScreenUpdating = True
Dim mainshseet As Worksheet
Set mainsheet = ActiveWorkbook.Worksheets("Q2")

Dim fRg As Range
Dim lr, i As Long
lr = mainsheet.Cells(Rows.Count, "B").End(xlUp).Row
Set fRg = mainsheet.Cells.Find(what:="Total", lookat:=xlWhole)
For i = lr To (fRg.Row + 1) Step -1
mainsheet.Cells(i, 1).EntireRow.Hide
Next i

End Sub

Can anyone solve this for me please?

792	EDQ V Fuse : .	Janette Morris	HHDC
	Total

 

[Fluff]

Apologies but It has a next i

so required a FOR to be in there?

Wrong, just do what I said in post#6

 

[Cooki]

i did and it comes up with the same error - Compile error "Next without for"

 

[Fluff]

In that case you did not do what I said, if you had there would not be a Next to give an error & you would have

Sub DeleteRowsBelow()
  
   Application.ScreenUpdating = True
   Dim mainshseet As Worksheet
   Set mainsheet = ActiveWorkbook.Worksheets("Q2")
  
   mainsheet.Unprotect Password:="retsup#qu2021"

   Dim fRg As Range
   Dim lr, i As Long

   Set fRg = mainsheet.Cells.Find(what:="Total", lookat:=xlWhole)
   fRg.Offset(1).Resize(Rows.Count - fRg.Row).EntireRow.Hidden = True

   mainsheet.Protect Password:="retsup#qu2021", AllowFiltering:=True, AllowSorting:=True
End Sub

 

[Cooki]

In that case you did not do what I said, if you had there would not be a Next to give an error & you would have

Sub DeleteRowsBelow()
 
   Application.ScreenUpdating = True
   Dim mainshseet As Worksheet
   Set mainsheet = ActiveWorkbook.Worksheets("Q2")
 
   mainsheet.Unprotect Password:="retsup#qu2021"

   Dim fRg As Range
   Dim lr, i As Long

   Set fRg = mainsheet.Cells.Find(what:="Total", lookat:=xlWhole)
   fRg.Offset(1).Resize(Rows.Count - fRg.Row).EntireRow.Hidden = True

   mainsheet.Protect Password:="retsup#qu2021", AllowFiltering:=True, AllowSorting:=True
End Sub

I do apologies, i defo miss understood.

ran that and get run time error '9' Subscript out of range

 

[Fluff]

Which line gives the error?

 

[Cooki]

Which line gives the error?

Sorted

changed activeworkbook to thisworkbook

and runs perfect

Thank you for your help, very much appreciated

 

[Fluff]

You're welcome & thanks for the feedback.