Thread: Find last row of A:A with data and stop

I have a while loop to color my rows down to row 2000 but all the time color way more than needed.

I like to change the while loop or replace with better code statement that will see last row of (A:A) that has data and stop there, then to keep color code rows to 2000.

Code:

Sub UpdateKitListColor()
'
' UpdateKitListColor Macro
' Update Color for Kit List parts
    
    Dim LRow As Integer
    Dim ACell As String
    Dim LCell As String
    Dim LColorCells As String
    Dim LColorCellsA2I As String
    Dim LColorCellsJ2O As String
    Dim LColorCellsP2T As String
    Dim LColorCellsU2A0 As String
    Dim LColorCellsAP2BU As String
    
    'Start at row 3
    LRow = 3
   
    ' Select sheet to color code 
    Sheets("BOM").Select
  
    'Update row colors for the first 2000 rows
    While LRow < 2000

        ACell = "A" & LRow
        'Items in Column "C" and the present LRow
        LCell = "H" & LRow
        
        'Color will changed in columns A to K
        LColorCells = "A" & LRow & ":" & "BU" & LRow
        LColorCellsA2I = "A" & LRow & ":" & "I" & LRow
        LColorCellsJ2O = "J" & LRow & ":" & "O" & LRow
        LColorCellsP2T = "P" & LRow & ":" & "T" & LRow
        LColorCellsU2AO = "U" & LRow & ":" & "AO" & LRow
        LColorCellsAP2BU = "AP" & LRow & ":" & "BU" & LRow
        
        Select Case Left(Range(LCell).Value, 6)
            
            'Set row color to Yellow
            Case "KT"
                Range(LColorCells).Interior.ColorIndex = 15
                Range(LColorCells).Interior.Pattern = xlSolid
                              
            'Default all other rows to no color
            Case Else
                Rows(LRow & ":" & LRow).Select
                'Range(LColorCells).Interior.ColorIndex = xlNone
                'WHITE COLUMN A-I
                'Range(LColorCellsA2I).Interior.ColorIndex = 2
                Range(LColorCellsA2I).Interior.Pattern = xlNone
                'LIGHT BLUE COLUMN J-O
                Range(LColorCellsJ2O).Interior.ColorIndex = 34
                Range(LColorCellsJ2O).Interior.Pattern = xlSolid
                'LIGHT GREEN COLUMN P-T
                Range(LColorCellsP2T).Interior.ColorIndex = 35
                Range(LColorCellsP2T).Interior.Pattern = xlSolid
                'LIGHT PINK COLUMN U-AO
                Range(LColorCellsU2AO).Interior.ColorIndex = 40
                Range(LColorCellsU2AO).Interior.Pattern = xlSolid
                'WHITE COLUMN AP-BU
                'Range(LColorCellsAP2BU).Interior.ColorIndex = 2
                Range(LColorCellsAP2BU).Interior.Pattern = xlNone
            
        End Select
              
        LRow = LRow + 1
        
    Wend
     
    Range("A1").Select
     
    'Sheets("TOOLS").Select
    
End Sub

So, this isn't an Access question?

Thread moved...

I first posted in wrong fourm and this seem to have gotten lost because of me please see attached Sample of the color code.

Marco on Worksheet "TOOLS" and controls color on worksheet "BOM"

When you click the macro it color codes rows 3 - 2000 I just like it to color code the rows with data (A3:A) as each BOM project has its own parts kit and number of items.

MB

Figure it out fairly easy was over looking first idea..
Here the change I did in the macro

Code:

    Dim Rng As Range
    
    'Start at row 3
    LRow = 3
     
    Sheets("BOM").Select
  
  With Sheets("BOM")
  Set Rng = .Range("A3", .Range("A" & Rows.Count).End(xlUp))
  End With
  
    'Update row colors for the first 2000 rows
    While LRow < Rng.Count + 3

MB