Hi K,
1) I guess the task is to hide not to delete
Thank you - yes I mis-read!
so probably:
Columns(2).SpecialCells(xlCellTypeBlanks).EntireRo w.Hidden=True
With the qualification of my follow-up post that columns(2) should read
Columns(1), for obvious reasons.
2) This method has a limitation of max 8,192 non-contiguous cells
I am aware of the problem and have frequently referred to this in other
posts.
It should be noted, however, that the constraining factor is independent of
the number of blank cells. To demonstrate this try:
'=============
Public Sub Demo()
Dim i As Long
Dim rng As Range, rng1 As Range
Application.ScreenUpdating = False
Set rng = Range("A1").Resize(8192 * 3 - 2)
rng.Value = "XYZ"
For i = 1 To rng.Count Step 3
Cells(i, 1).Resize(2).Clear
Next i
Set rng1 = rng.SpecialCells(xlCellTypeBlanks)
rng1.Interior.ColorIndex = 6
Debug.Print "rng1.Cells.Count ", rng1.Cells.Count
Debug.Print "rng1.Areas.Count ", rng1.Areas.Count
Application.ScreenUpdating = True
End Sub
'<<=============
---
Regards,
Norman
"KL" wrote in message
...
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?