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]

Are you trying to hide every single row from Total to row 1,048,576?

 

[Cooki]

Are you trying to hide every single row from Total to row 1,048,576?

the rows are already hidden, but each time i delete for example 5 rows that are blank, 5 rows unhide under total

if that makes sence

 

[Fluff]

Ok, how about

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

 

[Cooki]

Ok, how about

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

Im getting compile error "next without for"

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
lr = mainsheet.Cells(Rows.Count, "B").End(xlUp).Row 'Assumes rowdata existsin column A
Set fRg = mainsheet.Cells.Find(what:="Total", lookat:=xlWhole)
fRg.Offset(1).Resize(Rows.Count - fRg.Row).EntireRow.Hidden = True
mainsheet.Cells(i, 1).EntireRow.Hide
Next i
mainsheet.Protect Password:="retsup#qu2021", AllowFiltering:=True, AllowSorting:=True
End Sub

 

[Fluff]

Remove these two lines

mainsheet.Cells(i, 1).EntireRow.Hide
Next i

 

[Cooki]

Remove these two lines

mainsheet.Cells(i, 1).EntireRow.Hide
Next i

Ah the FOR has been taken out, ive added it back in back guess thats incorrect as not working

Sub hideRowsBelow()

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
lr = mainsheet.Cells(Rows.Count, "B").End(xlUp).Row 'Assumes rowdata existsin column A
Set fRg = mainsheet.Cells.Find(what:="Total", lookat:=xlWhole)
fRg.Offset(1).Resize(Rows.Count - fRg.Row).EntireRow.Hidden = True
For i = lr To (fRg.Row + 1) Step -1
mainsheet.Cells(i, 1).EntireRow.Hide
Next i
mainsheet.Protect Password:="retsup#qu2021", AllowFiltering:=True, AllowSorting:=True
End Sub

 

[Fluff]

Why are you adding stuff back & ignoring what I said?

 

[Cooki]

Why are you adding stuff back & ignoring what I said?

Apologies but It has a next i

so required a FOR to be in there?

apolgies if im wrong, but thats what the errors is saying, even when i just put in what you suggested?

 

[Cooki]

Apologies but It has a next i

so required a FOR to be in there?

apolgies if im wrong, but thats what the errors is saying, even when i just put in what you suggested?

Just doubled and checked and get the same error - Compile Error "Next without For"

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)
fRg.Offset(1).Resize(Rows.Count - fRg.Row).EntireRow.Hidden = True
mainsheet.Cells(i, 1).EntireRow.Hide
Next i

End Sub