Hide/Unhide Blank Rows, Add serial Numbers & Find Last Row

[Priyantha]

Dear All,

I used the below codes to hide/unhide blank rows in my range( A3:J16) and add serial numbers.

Even if the blank rows are hidden correctly, if there is no data in Cell "I14" (according to my sheet) related to the last row of my area, that row will not be unhidden.

Maybe it's because the method I used to select the last row may not be correct. According to my sheet, the last row that I need in column B is row 14, to identify it, I added a new blank row below the last row. Furthermore, I hope to get the total amount to Cell "I16". Is there any way to remove this blank line and get the last lines?

please help me to get the last row & unhide rows.

Code for Hide

Code: Select all

Option Explicit
Sub Printing()

Dim ws As Worksheet
Dim lr As Long
Dim j As Long

Dim totalRows As Long
Dim rowCounter As Long
Dim i As Long

Set ws = ThisWorkbook.Sheets("Thirdparty")
lr = ws.Range("B4").End(xlDown).Row
rowCounter = 1


''Sort as Thirdpaarty Code''

Dim Rng As Range
Dim SRng As Range

Set Rng = ws.Range("B3:J3").End(xlDown)
Set SRng = ws.Range("B3", Range("B3").End(xlDown))

Rng.Sort Key1:=SRng, Order1:=xlAscending, Header:=xlYes

''Hide For Blank Rows & "0"''''

For j = 4 To lr

If ws.Cells(j, 9).Value = "" Then
ws.Rows(j).Hidden = True
ElseIf ws.Cells(j, 9).Value = 0 Then
ws.Rows(j).Hidden = True
End If

'' Serial Numbers'''

If ActiveSheet.Rows(j).Hidden = False Then
    Range("A" & j).Value = rowCounter
    rowCounter = rowCounter + 1
End If

Next j

If ws.Cells(1, 1).Value = "Salary & Part Payment for the month of" Then
ws.Rows(lr + 6).Hidden = True
ws.Rows(lr + 7).Hidden = True
ws.Rows(lr + 8).Hidden = True
ws.Rows(lr + 9).Hidden = True
ws.Rows(lr + 10).Hidden = True

Else
ws.Rows(lr + 6).Hidden = False
ws.Rows(lr + 7).Hidden = False
ws.Rows(lr + 8).Hidden = False
ws.Rows(lr + 9).Hidden = False
ws.Rows(lr + 10).Hidden = False

End If

ActiveSheet.PrintOut

End Sub

Code For Unhide

Code: Select all

Option Explicit

Sub Clear()
Dim ws As Worksheet
Dim lr As Long
Dim j As Long
Dim rowCounter As Long

Set ws = ThisWorkbook.Sheets("Thirdparty")
lr = ws.Range("B4").End(xlDown).Row
rowCounter = 1

For j = 4 To lr
ws.Cells(j, 9).ClearContents
ws.Cells(j, 10).ClearContents

If ws.Cells(j, 9).Value = "" Then
ws.Rows(j).Hidden = False
ElseIf ws.Cells(j, 9).Value = 0 Then
ws.Rows(j).Hidden = False
End If

'' Serial Numbers'''

If ws.Rows(j).Hidden = False Then
    Range("A" & j).Value = rowCounter
    rowCounter = rowCounter + 1
End If

Next j

ws.Rows(lr + 6).Hidden = False
ws.Rows(lr + 7).Hidden = False
ws.Rows(lr + 8).Hidden = False
ws.Rows(lr + 9).Hidden = False
ws.Rows(lr + 10).Hidden = False
ws.Rows(lr + 11).Hidden = False

End Sub

[HansV]

The problem is that End(xlDown) looks at visible cells, so it won't find the last non-blank cell if that cell is hidden.
Apart from that, the code for Clear is more complicated than necessary.

Code: Select all

Sub Clear()
    Dim ws As Worksheet
    Dim lr As Long
    Dim j As Long

    Set ws = ThisWorkbook.Sheets("Thirdparty")
    lr = 3
    Do While ws.Cells(lr + 1, 2).Value <> ""
        lr = lr + 1
    Loop

    With ws.Range(ws.Cells(4, 9), ws.Cells(lr, 10))
        .ClearContents
        .EntireRow.Hidden = False
    End With

    For j = 4 To lr
        Range("A" & j).Value = j - 3
    Next j

    ws.Range(ws.Cells(lr + 6, 1), ws.Cells(lr + 11, 1)).EntireRow.Hidden = False
End Sub

[Priyantha]

Dear Hans,

Thanks for your cooperation,

BR,

Priyantha