CODEWITHSUNDEEP
vbaexcel
newUser
newUser

Reputation: 102

Excel VBa: How to hide rows based on number of rows after empty cell?

I'm new to Visual Basic and I need your help. I have this Worksheet on Excel that have this example:

Image

What I need to do is to make a button with a VBA code that allows me to Hide all the empty rows after the two empty rows after the last written cell. It should be something like this:

Image2

I can make all the empty cells disappear, which is easy. The thing is I don't want to make disappear all the empty cells, just the the empty cells after the two empty cells after the last written cell. I don't know if I'm making it clear enough.

Upvotes: 0

Views: 62

Answers (1)

Bitoubi
Bitoubi

Reputation: 116

Maybe something like this? (assuming you have less than 1000 rows)

Sub test()

    i = 1

While i < 1000

    If Cells(i, 1) = "" And Cells(i + 1) = "" Then
    i = i + 2
    While Cells(i, 1) = "" and i < 1000
        Cells(i, 1).EntireRow.Hidden = True
        i = i + 1
    Wend

    End If

    i = i + 1
Wend

End Sub

Upvotes: 1

Related Questions