software

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?uke??ezt ?rta:

gt; I have a spreadsheet which contains a lot of rows with no data, I would like
gt; to create a macro to hide these rows.
gt;
gt; Preferably I would like it to work off one column and hide any rows with a
gt; value of zero.
gt;
gt; 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(quot;Sheet1quot;)

lastrow = .Range(quot;A65536quot;).End(xlUp).Row

For i = 1 To lastrow

If .Range(quot;Aquot; amp; i).Value = quot;quot; Then

Rows(i amp; quot;:quot; amp; 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

quot;Lukequot; wrote:

gt; I have a spreadsheet which contains a lot of rows with no data, I would like
gt; to create a macro to hide these rows.
gt;
gt; Preferably I would like it to work off one column and hide any rows with a
gt; value of zero.
gt;
gt; Any ideas?

another way without macros:

1) select one of the columns where empty cells correspond to empty rows
2) menu Editgt;Goto...
3) press the button 'Special...'
4) mark the 'Blanks' option and press 'OK'
5) menu Formatgt;Rowsgt;Hide...

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,
KLquot;Lukequot; gt; wrote in message ...
gt;I have a spreadsheet which contains a lot of rows with no data, I would like
gt; to create a macro to hide these rows.
gt;
gt; Preferably I would like it to work off one column and hide any rows with a
gt; value of zero.
gt;
gt; 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 - www.officehelp.inHi Luke,

As an alternative, try:

Try:

'=============gt;gt;
Public Sub Tester()

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

End Sub
'lt;lt;=============---
Regards,
Normanquot;Lukequot; gt; wrote in message
...
gt;I have a spreadsheet which contains a lot of rows with no data, I would
gt;like
gt; to create a macro to hide these rows.
gt;
gt; Preferably I would like it to work off one column and hide any rows with a
gt; value of zero.
gt;
gt; Any ideas?
Hi Luke:

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

Was intended to read::

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

---
Regards,
Normanquot;Norman Jonesquot; gt; wrote in message
...
gt; Hi Luke,
gt;
gt; As an alternative, try:
gt;
gt; Try:
gt;
gt; '=============gt;gt;
gt; Public Sub Tester()
gt;
gt; On Error Resume Next
gt; Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
gt; On Error GoTo 0
gt;
gt; End Sub
gt; 'lt;lt;=============
gt;
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt; quot;Lukequot; gt; wrote in message
gt; ...
gt;gt;I have a spreadsheet which contains a lot of rows with no data, I would
gt;gt;like
gt;gt; to create a macro to hide these rows.
gt;gt;
gt;gt; Preferably I would like it to work off one column and hide any rows with
gt;gt; a
gt;gt; value of zero.
gt;gt;
gt;gt; Any ideas?
gt;
gt;

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:
gt; Luke,
gt;
gt; try this VBA code: (this code consider the name of the sheet as Sheet1
gt; and
gt; you want to hide the rows that the cell in the column A is empty)
gt;
gt; Sub HideEmptyRows()
gt;
gt; With Worksheets(quot;Sheet1quot;)
gt;
gt; lastrow = .Range(quot;A65536quot;).End(xlUp).Row
gt;
gt; For i = 1 To lastrow
gt;
gt; If .Range(quot;Aquot; amp; i).Value = quot;quot; Then
gt;
gt; Rows(i amp; quot;:quot; amp; i).EntireRow.Hidden = True
gt;
gt; End If
gt;
gt; Next i
gt;
gt; End With
gt;
gt; End Sub
gt;
gt; i hope this can help you!
gt;
gt; Have a nice Christmas end a great New Year!
gt;
gt; Pedro
gt;
gt; quot;Lukequot; wrote:
gt;
gt; gt; I have a spreadsheet which contains a lot of rows with no data, I
gt; would like
gt; gt; to create a macro to hide these rows.
gt; gt;
gt; gt; Preferably I would like it to work off one column and hide any rows
gt; with a
gt; gt; value of zero.
gt; gt;
gt; gt; Any ideas?--
gandhi318Posted from - www.officehelp.injust 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):
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,
KLquot;Norman Jonesquot; gt; wrote in message ...
gt; Hi Luke,
gt;
gt; As an alternative, try:
gt;
gt; Try:
gt;
gt; '=============gt;gt;
gt; Public Sub Tester()
gt;
gt; On Error Resume Next
gt; Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Delete
gt; On Error GoTo 0
gt;
gt; End Sub
gt; 'lt;lt;=============
gt;
gt;
gt; ---
gt; Regards,
gt; Norman
gt;
gt;
gt; quot;Lukequot; gt; wrote in message ...
gt;gt;I have a spreadsheet which contains a lot of rows with no data, I would like
gt;gt; to create a macro to hide these rows.
gt;gt;
gt;gt; Preferably I would like it to work off one column and hide any rows with a
gt;gt; value of zero.
gt;gt;
gt;gt; Any ideas?
gt;
gt;Hi,

gt; your suggested micro is not working

What do you mean it quot;is not workingquot;? 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(quot;Sheet1quot;)
lastrow = .Range(quot;A65536quot;).End(xlUp).Row
For i = 1 To lastrow
If .Range(quot;Aquot; amp; i).Value = quot;quot; 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.

gt; please check yourself and see a revised one is suggested

You can guess...;-)

Regards,
KL