Dynamic Range LastRow (Excluding Last Row)

[lamarh755]

I have a macro that I am trying to write that counts the total number of yellow, red and green cells in Column V (V8 to V Last Row Minus 1 Row) of Sheet "Day Shift". I am trying to have the total count of colors appear on Sheet "Summary" Range D20 for Green, F20 for Yellow and H20 for Red. I have a code but the code includes the last row, which I am trying to exclude. Any assistance with this code would be greatly appreciated. I am new to this. Here is what I have

Sheets("Day Shift").Activate

Dim rng As Range, rCell As Range, Total As Double, LastRow As Long

LastRow = Cells(Cells.Rows.Count, "V").End(xlUp).Row
Set rng = Range("V8:V" & LastRow)


For Each rCell In rng
If rCell.DisplayFormat.Interior.Color = RGB(255, 255, 0) Then _
Total = Total + 1
Next rCell

Sheets("Summary").Activate

Sheets("Summary").Range("F20") = Total

 

[Michael M]

Try using

Set rng = Range("V8:V" & LastRow-1)

 

[lamarh755]

I must be doing something wrong. I entered the range as recommended and it is still giving me a total of 9 yellow cells when it should be giving me 8.

 

[Michael M]

set your total variable at the beginning to zero

Sheets("Day Shift").Activate
Dim rng As Range, rCell As Range, Total As Double, LastRow As Long
Total = 0
LastRow = Cells(Cells.Rows.Count, "V").End(xlUp).Row
Set rng = Range("V8:V" & LastRow)
For Each rCell In rng
If rCell.DisplayFormat.Interior.Color = RGB(255, 255, 0) Then _
Total = Total + 1
Next rCell
Sheets("Summary").Activate
Sheets("Summary").Range("F20") = Total

 

[lamarh755]

I’m still get a 9 instead of an 8.

 

[lamarh755]

The grand total cell in the last row happens to be yellow. When I plug another color (red or green) into the code I get the correct count. Seems to still be an issue with it counting the last row in the range.

 

[Peter_SSs]

Try this instead. BTW, when posting code, please use code tags - see my signature block below for more on that.

LastRow = Columns("V").Find(What:="*", SearchDirection:=xlPrevious).Row
Set rng = Range("V8:V" & LastRow - 1)

 

[lamarh755]

That worked!!! Thank you and I will use code tags from now on.

 

[Peter_SSs]

That worked!!! Thank you

You're welcome.

Using
.End(xlUp)
with formal tables is problematic as it finds the bottom of the table, not necessarily the last data in the column.

 

[lamarh755]

Okay, thank you. I will keep that in mind.