Macro for hiding rows

I have a spreadsheet which contains a lot of rows with no data, I would like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows with a
value of zero.

Any ideas?

I think, that no macro is necessary to to this job! Simply apply an
Autofilter and choose Not empty when clicking the drop-down arrow!

Regards,
Stefi


„Lukeâ€� ezt Ã*rta:

I have a spreadsheet which contains a lot of rows with no data, I would like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows with a
value of zero.

Any ideas?

Luke,

try this VBA code: (this code consider the name of the sheet as Sheet1 and
you want to hide the rows that the cell in the column A is empty)

Sub HideEmptyRows()

With Worksheets("Sheet1")

lastrow = .Range("A65536").End(xlUp).Row

For i = 1 To lastrow

If .Range("A" & i).Value = "" Then

Rows(i & ":" & i).EntireRow.Hidden = True

End If

Next i

End With

End Sub

i hope this can help you!

Have a nice Christmas end a great New Year!

Pedro

"Luke" wrote:

I have a spreadsheet which contains a lot of rows with no data, I would like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows with a
value of zero.

Any ideas?

another way without macros:

1) select one of the columns where empty cells correspond to empty rows
2) menu EditGoto...
3) press the button 'Special...'
4) mark the 'Blanks' option and press 'OK'
5) menu FormatRowsHide...

or using short cut keys:

1) having the cursor in a cell of the relevant column press Ctrl+Space
2) Ctrl+g
3) Hold Alt+ s, k,
4) Enter
5) Hold Alt + o, r, h

Regards,
KL


"Luke" wrote in message ...
I have a spreadsheet which contains a lot of rows with no data, I would like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows with a
value of zero.

Any ideas?

it is looking easy to hide blank cell rows rather containing zero value
while i exercise on your point you may try the following for hiding the
blank cell rows
Ctrl+G (Go To Command) or Edit I Go To I
Select Special
Select Blank
Enter or Click OK
The above process/commands selects all bank cells. Then
Format I Row I Hide


--
gandhi318Posted from - http://www.officehelp.in

Hi Luke,

As an alternative, try:

Try:

'=============
Public Sub Tester()

On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
On Error GoTo 0

End Sub
'=============


---
Regards,
Norman


"Luke" wrote in message
...
I have a spreadsheet which contains a lot of rows with no data, I would
like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows with a
value of zero.

Any ideas?

Hi Luke:

Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete

Was intended to read::

Columns(1).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete

---
Regards,
Norman


"Norman Jones" wrote in message
...
Hi Luke,

As an alternative, try:

Try:

'=============
Public Sub Tester()

On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
On Error GoTo 0

End Sub
'=============


---
Regards,
Norman


"Luke" wrote in message
...
I have a spreadsheet which contains a lot of rows with no data, I would
like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows with
a
value of zero.

Any ideas?

Sir
your suggested micro is not working
please check yourself and see a revised one is suggested
you may see my reply which hides emptry cell rows









PedroPastre Wrote:
Luke,

try this VBA code: (this code consider the name of the sheet as Sheet1
and
you want to hide the rows that the cell in the column A is empty)

Sub HideEmptyRows()

With Worksheets("Sheet1")

lastrow = .Range("A65536").End(xlUp).Row

For i = 1 To lastrow

If .Range("A" & i).Value = "" Then

Rows(i & ":" & i).EntireRow.Hidden = True

End If

Next i

End With

End Sub

i hope this can help you!

Have a nice Christmas end a great New Year!

Pedro

"Luke" wrote:

I have a spreadsheet which contains a lot of rows with no data, I
would like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows
with a
value of zero.

Any ideas?


--
gandhi318Posted from - http://www.officehelp.in

just to add two comments:

1) I guess the task is to hide not to delete, so probably:
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Hidden=True

2) This method has a limitation of max 8,192 non-contiguous cells (otherwise it includes the whole column/row/sheet):
http://support.microsoft.com/kb/832293/en-us For this to become an issue in this specific example one needs to have at least 16385
rows where every other row is blank (so the risk might be remote).

Regards,
KL


"Norman Jones" wrote in message ...
Hi Luke,

As an alternative, try:

Try:

'=============
Public Sub Tester()

On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
On Error GoTo 0

End Sub
'=============


---
Regards,
Norman


"Luke" wrote in message ...
I have a spreadsheet which contains a lot of rows with no data, I would like
to create a macro to hide these rows.

Preferably I would like it to work off one column and hide any rows with a
value of zero.

Any ideas?

Hi,

your suggested micro is not working

What do you mean it "is not working"? Can you please be more specific? It does to me, only it is slow, blinking and may need some
optimization. Perhaps:

Sub HideEmptyRows()
Application.ScreenUpdating=False
With Worksheets("Sheet1")
lastrow = .Range("A65536").End(xlUp).Row
For i = 1 To lastrow
If .Range("A" & i).Value = "" Then
Rows(i ).EntireRow.Hidden = True
End If
Next i
End With
Application.ScreenUpdating=True
End Sub

Also if there is a significant number of rows I would use the SpecialCells one (see the the reply by Norman Jones) as it is way
faster.

please check yourself and see a revised one is suggested

You can guess...;-)

Regards,
KL