Finding Last Row/Column to Hide other Cells

[riedyp]

Hello,
I am trying to finding the last row with a value.
-I know how to do this typically by using "xlUp" but for some reason it is stopping at a cell that does not contain a value but just a change in color.
-I am now trying to find a different way to go about this.
Instead, I want to go Row by row to find the last column of every Row(starting at 9) until the column number equals 4.

Below is my code which I realize does not really make sense, but maybe shows you a little of what I am thinking.

Sub HiddenRows()
Dim StartRow As Long, LastColumn As Long
LastColumn = Worksheets("Project - Gantt Chart").Cells(StartRow, Columns.Count).End(xlLeft).Column 'find the last column
StartRow = Worksheets("Project - Gantt Chart").Cells(9,)
For newRow = StartRow To 100 Step 1                        'newRow is the counter. It will count from the last row to row 1 with a -1 step size.

    Next
End Sub

 

[GWteB]

This line of code will not work:

LastColumn = Worksheets("Project - Gantt Chart").Cells(StartRow, Columns.Count).End(xlLeft).Column 'find the last column

change xlLeft into xlToLeft
Whenever {xlUp} is not working as expected, the cell on the last row is not empty. The interiour color of a cell is not the cause of your problem.

 

[riedyp]

Thank you, any idea why the attached code is not working? It is giving me "application-defined or object-defined error".

Sub HiddenRows()
Dim CheckRow As Long, HiddenRng As Range
HiddenRng = Range("CheckRow:100")
For CheckRow = 9 To 100 Step 1      'newRow is the counter. It will count from the last row to row 1 with a -1 step size.
    If Worksheets("Project - Gantt Chart").Cells(CheckRow, Columns.Count).End(xlToLeft).Column = 4 Then
    Worksheets("Executive Summary").Rows(HiddenRng).Hidden = True
    
End If
Next
End Sub

 

[GWteB]

Thank you, any idea why the attached code is not working? It is giving me "application-defined or object-defined error".

You are welcome. Below I will make some remarks on your code. Note that I do not claim to be complete.
HiddenRng = Range("CheckRow:100")
Ranges are referenced with the SET statement as in Set HiddenRng = xxxxx , so this line will error.
"CheckRow:100" as used by you is a string which has to be a name of an (existing) address, as in "A1:B2"
Worksheets("Executive Summary").Rows(HiddenRng).Hidden = True
This line will error since the Rows collection expects a number (or a placeholder or variable that represents a number).
You've given the variable HiddenRng the dimension of a Range, so HiddenRng will be "Nothing".

 

[MARK858]

-I know how to do this typically by using "xlUp" but for some reason it is stopping at a cell that does not contain a value but just a change in color.

Are you sure that the cell doesn't contain a formula returning ""?