VBA: how to Unhide first blank row in a range

GuyGadois

Active Member
Joined
Jan 12, 2009
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
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:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Try this:
Code:
Cells(Rows.count, "A").End(xlUp).Offset(1).EntireRow.Hidden = False
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
Try:

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

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top