ExcelBanter - Hiding Rows

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - Hiding Rows (https://www.excelbanter.com/excel-worksheet-functions/183663-hiding-rows.html)

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.
--
Lee

Lee,

Right click the sheet tab, view code and paste this in and run it

Sub sonic()
Dim myrange As Range
Set myrange = Range("G5:G29")
For Each c In myrange
If IsEmpty(c) Then
c.EntireRow.Hidden = True
End If
Next
End Sub

Mike

"Lee" wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.
--
Lee

Have you tried using AutoFilters?

"Lee" wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.
--
Lee

Is this a typo.......... both rows 8 & 10?

Should it be 8 & 12?

Select G5:G29 and F5SpecialBlanksOK

Hold CTRL key and Right-click on the row header of active cell and "Hide"

Gord Dibben MS Excel MVP

On Mon, 14 Apr 2008 06:33:01 -0700, Lee wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.

Thanks, I entered the code as given but nothing happened. I have never
entered a code before. So what do I have to do next.
--
Lee

"Mike H" wrote:

Lee,

Right click the sheet tab, view code and paste this in and run it

Sub sonic()
Dim myrange As Range
Set myrange = Range("G5:G29")
For Each c In myrange
If IsEmpty(c) Then
c.EntireRow.Hidden = True
End If
Next
End Sub

Mike

"Lee" wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.
--
Lee

Thanks for your suggestion but when I select the range and do
F5SpecialBlanksOK I get the message No cells were found.

I know how to hide individual rows by clicking at the row header. But some
times there are quite a few rows that are blank and I wanted to automate the
task.

--
Lee

"Gord Dibben" wrote:

Is this a typo.......... both rows 8 & 10?

Should it be 8 & 12?

Select G5:G29 and F5SpecialBlanksOK

Hold CTRL key and Right-click on the row header of active cell and "Hide"

Gord Dibben MS Excel MVP

On Mon, 14 Apr 2008 06:33:01 -0700, Lee wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.

Yes, It was a typo. I am sorry about that.
--
Lee

"Gord Dibben" wrote:

Is this a typo.......... both rows 8 & 10?

Should it be 8 & 12?

Select G5:G29 and F5SpecialBlanksOK

Hold CTRL key and Right-click on the row header of active cell and "Hide"

Gord Dibben MS Excel MVP

On Mon, 14 Apr 2008 06:33:01 -0700, Lee wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.

I can't take credit for this bit of code that I collected along the way that
might do what you want. Another more experienced Excel user created it some
time ago.
------
Sub HideRows()

' Hides rows where G is blank

Dim R As Range
For Each R In Range("G5:G29")
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

End Sub
------
If you copy the information between the ---- breaks, you can paste them into
the workbook by right-clicking a tab and selecting View Code. On the left
side of the window there is generally an Explorer view of the workbook
sheets. Look for a folder titled Modules. Right-click that folder and select
Insert then Module. Paste this bit of code there and then close the vb
window. This is now a Macro that is availble to run from the Macros list. It
should be titled HideRows. Just click it and choose the run button.

"Lee" wrote:

Yes, It was a typo. I am sorry about that.
--
Lee

"Gord Dibben" wrote:

Is this a typo.......... both rows 8 & 10?

Should it be 8 & 12?

Select G5:G29 and F5SpecialBlanksOK

Hold CTRL key and Right-click on the row header of active cell and "Hide"

Gord Dibben MS Excel MVP

On Mon, 14 Apr 2008 06:33:01 -0700, Lee wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.

Hello Felone,

Thanks for the code and detailed instructions, but I am afraid the Code did
not work. May be reason is that the whole row is not blank.
My spreadsheet looks like this.
Employee Hours Rate Basic O/Time Other Wages

Employees names are always there. but if there are no wages for an employee
for that day or week then I want to hide the whole row for which the wages
cell is empty.
--
Lee

"Fleone" wrote:

I can't take credit for this bit of code that I collected along the way that
might do what you want. Another more experienced Excel user created it some
time ago.
------
Sub HideRows()

' Hides rows where G is blank

Dim R As Range
For Each R In Range("G5:G29")
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

End Sub
------
If you copy the information between the ---- breaks, you can paste them into
the workbook by right-clicking a tab and selecting View Code. On the left
side of the window there is generally an Explorer view of the workbook
sheets. Look for a folder titled Modules. Right-click that folder and select
Insert then Module. Paste this bit of code there and then close the vb
window. This is now a Macro that is availble to run from the Macros list. It
should be titled HideRows. Just click it and choose the run button.

"Lee" wrote:

Yes, It was a typo. I am sorry about that.
--
Lee

"Gord Dibben" wrote:

Is this a typo.......... both rows 8 & 10?

Should it be 8 & 12?

Select G5:G29 and F5SpecialBlanksOK

Hold CTRL key and Right-click on the row header of active cell and "Hide"

Gord Dibben MS Excel MVP

On Mon, 14 Apr 2008 06:33:01 -0700, Lee wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.

No problem Lee, just change the Range to where the blank cells would be.
Where you see ("G5:G29") can be changed to what ever column, or row you need
it to be. If the Wages column is not G, replace G with the correct column.
The code just looks in one column, if the cell in that column is blank, then
it hides the row that cell is found in. If it isn't working with the Wage
column blank, then the Wage column isn't G, or there is actually something in
the cell.

"Lee" wrote:

Hello Felone,

Thanks for the code and detailed instructions, but I am afraid the Code did
not work. May be reason is that the whole row is not blank.
My spreadsheet looks like this.
Employee Hours Rate Basic O/Time Other Wages

Employees names are always there. but if there are no wages for an employee
for that day or week then I want to hide the whole row for which the wages
cell is empty.
--
Lee

"Fleone" wrote:

I can't take credit for this bit of code that I collected along the way that
might do what you want. Another more experienced Excel user created it some
time ago.
------
Sub HideRows()

' Hides rows where G is blank

Dim R As Range
For Each R In Range("G5:G29")
If R.Value = "" Then Rows(R.Row).Hidden = True
Next

End Sub
------
If you copy the information between the ---- breaks, you can paste them into
the workbook by right-clicking a tab and selecting View Code. On the left
side of the window there is generally an Explorer view of the workbook
sheets. Look for a folder titled Modules. Right-click that folder and select
Insert then Module. Paste this bit of code there and then close the vb
window. This is now a Macro that is availble to run from the Macros list. It
should be titled HideRows. Just click it and choose the run button.

"Lee" wrote:

Yes, It was a typo. I am sorry about that.
--
Lee

"Gord Dibben" wrote:

Is this a typo.......... both rows 8 & 10?

Should it be 8 & 12?

Select G5:G29 and F5SpecialBlanksOK

Hold CTRL key and Right-click on the row header of active cell and "Hide"

Gord Dibben MS Excel MVP

On Mon, 14 Apr 2008 06:33:01 -0700, Lee wrote:

I want to hide all rows in which any cell from G5:G29 is blank. Lets say if
G8 & G12 are blanks then I would like to hide both rows 8 & 10 . Thanks.