CODEWITHSUNDEEP
vbaexcel
K Bizzy
K Bizzy

Reputation: 85

Hiding Empty Cells

I'm currently working on a code that hides empty cells ,but the problem is i want it to start hiding at a certain range ("A9:A12") not at the beginning of the sheet. here is my program : 

Sub EmptyRow()
'Dim s As String
po = Range("A9:A12").Count
Range("A8").Activate

For i = 1 To po
 s = i & ":" & i
 If IsEmpty(Cells(i, 1).Value) Then
     Rows(s).Select
     Selection.EntireRow.Hidden = True
End If
Next

End Sub

The program keeps on hiding cells from the beginning, how do I set it up so it deletes from the range i want it to. Please help.

Upvotes: 2

Views: 119

Answers (4)

Vityata
Vityata

Reputation: 43575

You can even make your code shorter like this:

For i = 9 To 12
    Cells(i, 1).EntireRow.Hidden = IsEmpty(Cells(i, 1).Value)
Next i

Thus, the result of the Hidden property would be dependent on whether the Cells(i,1) is empty. It is easier to understand and to maintain.

Upvotes: 4

Slai
Slai

Reputation: 22866

Sheets("Sheet1").Range("A9:A12").SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

SpecialCells results in run-time error if no cells are found, but that can be checked:

If [CountBlank(Sheet1!A9:A12)] Then _
    [Sheet1!A9:A12].SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

or ignored:

On Error Resume Next
[Sheet1!A9:A12].SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

Upvotes: 3

Egan Wolf
Egan Wolf

Reputation: 3573

Check the solution below. In case you need to change your affected area, just change the value of targetRange.

Sub EmptyRow()
Dim targetRange as Range, po as Long, i as Long
Set targetRange = Range("A9:A12")
po = targetRange.Count

With targetRange
    For i = 1 To po
        If IsEmpty(.Cells(i, 1).Value) Then
        .Rows(i).EntireRow.Hidden = True
        End If
    Next
End With

End Sub

Upvotes: 3

Tim Wilkinson
Tim Wilkinson

Reputation: 3801

You can get rid of bits like select

Sub EmptyRow()
    For i = 9 To 12
        If IsEmpty(Cells(i, 1).Value) Then
            Cells(i, 1).EntireRow.Hidden = True
        End If
    Next i
End Sub

Upvotes: 1

Related Questions