VBA: how to Unhide first blank row in a range
Results 1 to 6 of 6

Thread: VBA: how to Unhide first blank row in a range

  1. #1
    Board Regular
    Join Date
    Jan 2009
    Location
    SLO
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA: how to Unhide first blank row in a range

    I have a range "Sample_Range" which is basically A5:Y209. The sheet has some code to hide rows where there is no value in row A. This works fine.

    Code:
    Sub HideRows()
        On Error GoTo ProcError
            Application.ScreenUpdating = False
                Range("Hide_Row_Counter").Value = "Completed"
                Dim i As Long
                For i = 5 To Range("A5:A209").Count
                    If Len(Cells(i, 2)) = 0 Then
                        Rows(i).Hidden = True
                   End If
                Next
            Application.ScreenUpdating = True
            Range("Hide_Row_Counter").Value = "Completed"
    ProcError:
    End Sub
    I would like to figure out what code is needed to unhide the first row after the last entry in row A and only one row. The result would be all the visible rows after running the code above plus a new blank row (at least where A is blank. Is this possible?

    Thanks,

    Guy
    Last edited by GuyGadois; Aug 15th, 2019 at 06:59 PM.
    I use Excel 2016 for PC

  2. #2
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,964
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA: how to Unhide first blank row in a range

    Try this:
    Code:
    Cells(Rows.count, "A").End(xlUp).Offset(1).EntireRow.Hidden = False

  3. #3
    Board Regular
    Join Date
    Jan 2009
    Location
    SLO
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: how to Unhide first blank row in a range

    I tried that code but it doesn't seem to do anything. Did you mean for it to be a part of some existing code or in a sub by itself. Also, the sheet is very long so I only want the macro to check row A from A5 to A209, not all of A

    Cheers,

    Guy
    I use Excel 2016 for PC

  4. #4
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,964
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA: how to Unhide first blank row in a range

    Ok, try:

    Code:
    Cells(210, "A").End(xlUp).Offset(1).EntireRow.Hidden = False
    It's a sub by itself, run it after you run your code above.

  5. #5
    Board Regular
    Join Date
    Jan 2009
    Location
    SLO
    Posts
    306
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: how to Unhide first blank row in a range

    Perfect, thank you @Akuini.

    Can I ask you if it is then possible to assign the value "Test" to the "R" column in the row that was just unhidden?
    Last edited by GuyGadois; Aug 15th, 2019 at 07:47 PM.
    I use Excel 2016 for PC

  6. #6
    Board Regular Akuini's Avatar
    Join Date
    Feb 2016
    Location
    Indonesia
    Posts
    1,964
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    4 Thread(s)

    Default Re: VBA: how to Unhide first blank row in a range

    Try:

    Code:
    With Cells(210, "A").End(xlUp).Offset(1)
    .EntireRow.Hidden = False
    .Offset(, 17) = "Test"
    End With

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •