Thread: Run macro until the end of the sheet

I had some changes in the system, so now I need to run the macro until the end of the sheet (last row with data). Column A has data in every row.

Sub HideRows()
    BeginRow = 1
    EndRow = 60
    ChkCol = 4
    ChkCol2 = 6
    
    Sheets("27.09.-03.10.").Select
    Cells.Select
    Cells.EntireRow.AutoFit

    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, ChkCol).Value = 0 And Cells(RowCnt, ChkCol2).Value = 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End Sub

Thanks in advance.

Please explain exactly what you mean. A selected sheet isn't a row so your question is impossible to imagine.

Try again, please.


NOTE: Good job using the INDENT function to overcome the forum's current inability to let you color text when you use code tags instead. Very creative and your solution supports the "spirit" of the code tag rule which is make sure code indents itself properly to make it easy for everyone to read.

Well done.

So suppose I have selected a row - is it possible to make the macro understand the selected row as endrow?

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

In VBA you can solve problems dynamically. There's no reason you should ever have to "help" the macro find the last row with data in it. There are many, many standard methods for examining the data on the sheet and identifying the last row.

1) Look at a specific column that always is filled in for each row of data, like column A, look up from the bottom of the worksheet and find the last cell in column A with a value and mark that as the last row

Dim LR as Long

LR = Range("A" & Rows.Count).End(xlUp).Row

2) Examine every cell in the worksheet and find the last cell with data in it, mark that row as the last row regardless of which column it is in:

Dim LR as Long

LR = Cells.Find("*", Cells(Rows.Count, Columns.Count), _
   SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

3) Your suggestion for selected cell:

Dim LR as Long

LR = ActiveCell.Row

4) Your suggestion for cell value:

Dim LR as Long

LR = Range("H1").Value

Your issue is no longer clear. Please provide a sample workbook demonstrating completely the scenario. Use BEFORE/AFTER sheets if necessary to make it clear.

Click GO ADVANCED and use the paperclip icon to post up a desensitized copy of your workbook.

Here's a sample sheet.
Seems that the following code would work but I don't know how to insert it in the macro.

Dim LR as Long

LR = Range("A" & Rows.Count).End(xlUp).Row

Perhaps something like this?

Sub HideRows()
Dim LR as Long
LR = Range("A" & Rows.Count).End(xlUp).Row
    ChkCol = 4
    ChkCol2 = 6
    
    Sheets("27.09.-03.10.").Select
    Cells.Select
    Cells.EntireRow.AutoFit

    For LR
        If Cells(RowCnt, ChkCol).Value = 0 And Cells(RowCnt, ChkCol2).Value = 0 Then
            Cells(RowCnt, ChkCol).EntireRow.Hidden = True
        End If
    Next RowCnt
End Sub

As I said before, there are many ways to determine the last row of data, and yours is even more specific because you only "need" rows up through yesterday, right?

So for your macro, I would search for today's date and use that row as the basis for stopping, leaving all rows after that visible.

Then instead of looping through the rows one at a time, you can instantly make a RANGE using SpecialCells of all the blank cells in column D down through your last row, then do the same thing with column E, then find where there two ranges intersect (meaning blank in both D and E), and then hide the rows all at once with a single command.

This is how I would write your routine:

Option Explicit

Sub HideRows()
Dim LR As Long, RNG As Range
Dim RNG2 As Range, RNG3 As Range
Application.ScreenUpdating = False

'Find today's date on sheet and use as last row
    LR = Cells.Find(What:=Format(Date, "d-mmm-yy"), After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Row

'Make a range of all blank rows in column D
    Set RNG = Range("D3:D" & LR).SpecialCells(xlCellTypeBlanks).EntireRow
'Make a range of all blank rows in column E
    Set RNG2 = Range("E3:E" & LR).SpecialCells(xlCellTypeBlanks).EntireRow
'Make a range of where RNG and RNG2 share rows
    Set RNG3 = Intersect(RNG, RNG2)

'Hide all rows in RNG3
    RNG3.EntireRow.Hidden = True

'Cleanup
    Set RNG = Nothing
    Set RNG2 = Nothing
    Set RNG3 = Nothing
    Application.ScreenUpdating = True
End Sub

Of course the same thing can be shortened up but the resulting code is much less clearer what's going on, but this is effectively the same thing:

Option Explicit

Sub HideRows()
Dim LR As Long
Application.ScreenUpdating = False

'Find today's date on sheet and use as last row
    LR = Cells.Find(What:=Format(Date, "d-mmm-yy"), After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Row

'Hide all rows blank in column D and E
    Intersect(Range("D3:D" & LR).SpecialCells(xlCellTypeBlanks).EntireRow, _
        Range("E3:E" & LR).SpecialCells(xlCellTypeBlanks).EntireRow).EntireRow.Hidden = True

Application.ScreenUpdating = True
End Sub

Wow thanks, this is much better than the original code. One problem though - data for today will be added either today afternoon or tomorrow so how to make the code hide rows until yesterday, not today? Oh, and I also need rows with 0s in columns D and E hidden.

The code provided above clearly shows where the "find today's date" code is. Just subtract 1 from the date.

'Find today's date on sheet and use as last row
    LR = Cells.Find(What:=Format(Date - 1, "d-mmm-yy"), After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=True).Row

Too bad about the zeros thing, too, was hoping you could avoid looping row by row based on your sample sheet.

NOTE: Sample sheets should always fully represent your need, all variations. By dumbing it down even a little people end up wasting a lot of time on solutions/methods that won't pan out in the end.