CODEWITHSUNDEEP
vbaexcelexcel-2010
George
George

Reputation: 35

End row specification to stop at the last row of data

I created a macro which hides all rows where the value in a particular column for a particular cell does not exist.

I have this code:

At the moment endrow=6, this is running the macro up until the 6th row but what I infact want it to do is for the end row to be the last row of data and then stop.

I am unsure how to proceed whether to try doing a loop through column function.

Any ideas?

Sub Hidemacro()
BeginRow = 1
EndRow = 6
ChkCol = 5

For RowCnt = BeginRow To EndRow
    If Cells(RowCnt, ChkCol).Value = "" Then
        Cells(RowCnt, ChkCol).EntireRow.Hidden = True
    Else
        Cells(RowCnt, ChkCol).EntireRow.Hidden = False
    End If
Next RowCnt
End Sub

Upvotes: 0

Views: 1346

Answers (2)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Because I was in a mood, here is a "one-liner" that will hide all the rows that have an empty cell in ChkCol (col 1 here, but you can obviously change it)

Option Explicit
Sub Hidemacro()
    Const ChkCol As Long = 1
Range(Cells(1, ChkCol), Cells.Find("*", Cells(1, ChkCol), xlFormulas, , xlByRows, xlPrevious)).SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True
End Sub

To expand this concept to use in a macro to hide rows where the cell in ChkCol is either empty or contains a null string; and taking into consideration that explicitly defining the worksheet on which one is running the macro is usually a good thing (in the macro below, if you wish, you could explicitly Set WS = ActiveSheet, if you really wanted that functionality), I suggest the following, which might run faster if you have many rows:

Option Explicit
Sub Hidemacro()
    Const ChkCol As Long = 1
    Const BeginRow As Long = 1
    Dim WS As Worksheet
    Dim myRange As Range, R As Range

Set WS = Worksheets("sheet1")

Application.ScreenUpdating = False

With WS

.Cells.EntireRow.Hidden = False

With Range(.Cells(BeginRow, ChkCol), .Columns(ChkCol).Find( _
    what:="*", _
    after:=.Cells(BeginRow, ChkCol), _
    LookIn:=xlFormulas, _
    searchorder:=xlByRows, _
    searchdirection:=xlPrevious))

    .SpecialCells(xlCellTypeBlanks).EntireRow.Hidden = True

    For Each R In .SpecialCells(xlCellTypeVisible).Cells
        If Len(R.Text) = 0 Then R.EntireRow.Hidden = True
    Next R
End With

End With

Application.ScreenUpdating = True
End Sub

Upvotes: 0

Robin Mackenzie
Robin Mackenzie

Reputation: 19319

Try this instead:

Sub Hidemacro()

    'set a reference to active sheet
    Dim sht As Worksheet
    Set sht = Application.ActiveSheet

    'set macro variables
    Dim BeginRow As Long
    Dim EndRow As Long
    Dim ChkCol As Long

    'set up variables
    BeginRow = 1
    EndRow = sht.Cells(Rows.Count, "A").End(xlUp).Row
    Debug.Print EndRow
    ChkCol = 5

    'do the hiding of rows - your original code plus sht reference
    For RowCnt = BeginRow To EndRow
        If sht.Cells(RowCnt, ChkCol).Value = "" Then
            sht.Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        Else
            sht.Cells(RowCnt, ChkCol).EntireRow.Hidden = False
        End If
    Next RowCnt

End Sub

Upvotes: 0

Related Questions