Finding the last row in sheets automatically

[Olov]

Hello,
I have this code that I want to change so that it automatically finds the last row.

Sub Hide_Rows_With_0_in_Column_F()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim iRow As Range, rngLoop As Range
        For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case "K1"
            Set rngLoop = ws.Range("8:50").Rows
            For Each iRow In rngLoop
            If iRow.Cells(6) <= 0 Then
            iRow.Hidden = True
            End If
            Next iRow
        Case "K2"
            Set rngLoop = ws.Range("8:150").Rows
            For Each iRow In rngLoop
            If iRow.Cells(6) <= 0 Then
            iRow.Hidden = True
            End If
            Next iRow
        Case "K3"
            Set rngLoop = ws.Range("8:650").Rows
            For Each iRow In rngLoop
            If iRow.Cells(6) <= 0 Then
            iRow.Hidden = True
            End If
            Next iRow
        Case "K4"
            Set rngLoop = ws.Range("8:500").Rows
            For Each iRow In rngLoop
            If iRow.Cells(6) <= 0 Then
            iRow.Hidden = True
            End If
            Next iRow
        Case "K7"
            Set rngLoop = ws.Range("8:400").Rows
            For Each iRow In rngLoop
            If iRow.Cells(6) <= 0 Then
            iRow.Hidden = True
            End If
            Next iRow
        Case Else
        End Select
    Next ws
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

I have used

lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).row

but I can't get it into my code.
Can anyone help me?

 

[Kaper]

Is this what you are looking for?

Sub Hide_Rows_With_0_in_Column_F()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim irow As Range, rngLoop As Range, lastrow As Long
        For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case "K1", "K2", "K3", "K4", "K7"
            lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            Set rngLoop = ws.Range("8:" & lastrow).Rows
            For Each irow In rngLoop
            If irow.Cells(6) <= 0 Then
            irow.Hidden = True
            End If
            Next irow
        Case Else
        End Select
    Next ws
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

note difference between standard double quotes and "typographic" - you wrote:

lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).row

while it shall be:

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row

 

[Olov]

Is this what you are looking for?

Sub Hide_Rows_With_0_in_Column_F()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim irow As Range, rngLoop As Range, lastrow As Long
        For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case "K1", "K2", "K3", "K4", "K7"
            lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
            Set rngLoop = ws.Range("8:" & lastrow).Rows
            For Each irow In rngLoop
            If irow.Cells(6) <= 0 Then
            irow.Hidden = True
            End If
            Next irow
        Case Else
        End Select
    Next ws
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

note difference between standard double quotes and "typographic" - you wrote:

lastRow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).row

while it shall be:

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row

Thank you, Caper!
Works very well! Can you explain what what

“A” in row lastrow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row

means?

 

[Joe4]

Thank you, Caper!
Works very well! Can you explain what what

“A” in row lastrow = ws.Cells(ws.Rows.Count, “A”).End(xlUp).Row

means?

It is the column reference (column "A").
Note it has to be straight up-and-down double quote marks. VBA does not recognize slanted ones.

The format of Cells is:
Cells(row, column)

The row reference must be numeric.
But the column reference can be numeric or a letter.
So, if you want to refer to column M, you could either use "M" or 13, as M is the 13th column.

 

[Olov]

It is the column reference (column "A").
Note it has to be straight up-and-down double quote marks. VBA does not recognize slanted ones.

The format of Cells is:
Cells(row, column)

The row reference must be numeric.
But the column reference can be numeric or a letter.
So, if you want to refer to column M, you could either use "M" or 13, as M i

Ok. I understand. Maybe you can help me with the next step. I want rows with values B, D, I, K, R, S, or V in column E (5) to be hidden.

Sub Hide_Rows_Column_5()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim irow As Range, rngLoop As Range, lastrow As Long
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case "K1", "K2", "K3", "K4", "K7"
        lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
            Set rngLoop = ws.Range("8:" & lastrow).Rows
            For Each irow In rngLoop
            If irow.Cells(5) = "B" Then 'or B, D, I, K, R, S, V
            irow.Hidden = True
            End If
            Next irow
        Case Else
        End Select
    Next ws
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

I want to have B, D, I, K, R, S, V in

If irow.Cells(5) = "B" Then

 

[Joe4]

Ok. I understand. Maybe you can help me with the next step. I want rows with values B, D, I, K, R, S, or V in column E (5) to be hidden.

Sub Hide_Rows_Column_5()
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    Application.Calculation = xlManual
    Dim irow As Range, rngLoop As Range, lastrow As Long
    For Each ws In ThisWorkbook.Worksheets
        Select Case ws.Name
        Case "K1", "K2", "K3", "K4", "K7"
        lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).row
            Set rngLoop = ws.Range("8:" & lastrow).Rows
            For Each irow In rngLoop
            If irow.Cells(5) = "B" Then 'or B, D, I, K, R, S, V
            irow.Hidden = True
            End If
            Next irow
        Case Else
        End Select
    Next ws
    Application.Calculation = xlAutomatic
    Application.ScreenUpdating = True
End Sub

I want to have B, D, I, K, R, S, V in

If irow.Cells(5) = "B" Then

That is a different question altogether, and therefore should be asked in a new thread of its own.

 

[Olov]

Ok. Thank you for clarifying for me.