I have a question regarding the below picture, I need to check until the end of the columns.

the check always begins from column "L" but the end change from file to file how needed check.

The below code work very well, still only this small issue, Your help will be appreciated

Sub HighlightInvalidRows()

Application.ScreenUpdating = False

Dim i As Long
Dim c As Long

    ' Prepare.

    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    
    ' Reference the worksheet ('ws').
    Dim ws As Worksheet: Set ws = wb.Worksheets("Cumulated BOM")
    
    ' Reference the range ('rg').
    Dim rg As Range
    Set rg = ws.Range("L2", "S" & ws.Cells(ws.Rows.Count, "L").End(xlUp).Row)
    
    ' Write the number of columns of the range to a variable ('CellsCount').
    Dim CellsCount As Long: CellsCount = rg.Columns.Count
    ' Each row of the range has this number of columns (cells).
    
    ' Remove all range colors.
    rg.Interior.Color = xlNone
    
    ' Combine the rows ('rrg') to be highlighted
    ' into the Highlight range ('hrg').
    
    ' Declare variables that appear for the first time in the following loop.
    Dim hrg As Range
    Dim rrg As Range
    Dim MatchCount As Long
    
    ' Loop through the rows of the range.
    For Each rrg In rg.Rows
        ' Write the number of appearances of the value in the current row
        ' to a variable ('MatchCount').
        MatchCount = Application.CountIf(rrg, "-") Or Application.CountIf(rrg, "")
        ' Compare the match count with the cells count.
        If MatchCount = CellsCount Then ' the numbers are equal
            ' Combine the current row into the highlight range.
            If hrg Is Nothing Then ' the first match
                Set hrg = rrg
            Else ' all other matches
                Set hrg = Union(hrg, rrg)
                  
            End If
        End If
        
        
        
    Next rrg
        
    ' Highlight the rows (in one go) and inform.
    
    If hrg Is Nothing Then ' no matches found
        MsgBox "No Empty Penetration Found.", vbInformation
    Else ' matches found
        hrg.Interior.Color = RGB(255, 87, 87)
       
        
    End If

CodePudding user response：

You define the Range with this statement:

Set rg = ws.Range("L2", "S" & ws.Cells(ws.Rows.Count, "L").End(xlUp).Row)

You fetch the number of rows but hardcode the end column ("S").
There is a question here on SO about how to get the last used row/column/cell in Excel using VBA. Depending on the circumstances, it can get quite tricky, see Find last used cell in Excel VBA.

However, there are two things that you can easily try:

a) Simply use CurrentRegion:

Set rg = ws.Range("L2").CurrentRegion

b) The technique that is used most often to fetch the last row is the logic to "jump" to the last row and then jump back to the last row that is used. Think about as if you jump to the very end of your sheet by pressing Ctrl Down and then pressing Ctrl Up. Your code does already exactly that.
Similarly, you can get the last column by pressing Ctrl Right and then pressing Ctrl Left.

In Code this could look like that:

Dim lastRow As Long, lastCol As Long
With ws
    lastRow = .Cells(.Rows.Count, "L").End(xlUp).Row  ' Last row in use in Col L
    lastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column ' Last Col in use in row 2
    Set rg = .Range(.cells(2, "L"), .cells(lastRow, lastCol))
End With

CodePudding user response：

Reference a Part of a (Table) Range

• Note that the code is written for any range and you are having problems only with referencing the range dynamically.

• There are several ways to do this but I'll stick with the easiest, most commonly used way, described in more detail in FunThomas' answer.

• Replace the following lines...

  ' Reference the range ('rg').
  Dim rg As Range
  Set rg = ws.Range("L2", "S" & ws.Cells(ws.Rows.Count, "L").End(xlUp).Row)
  

  ... with these:

  ' In column 'L', determine the last row ('lRow'),
  ' the row of the bottom-most non-empty cell.
  Dim lRow As Long: lRow = ws.Cells(ws.Rows.Count, "L").End(xlUp).Row
  
  ' In row '1' (where the headers are), determine the last column ('lCol'),
  ' the column of the right-most non-empty cell.
  Dim lCol As Long: lCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
  
  ' Reference the range ('rg').
  Dim rg As Range: Set rg = ws.Range("L2", ws.Cells(lRow, lCol))