Untitled

Private Sub FilterAndRemoveBlankRows(wsReport As Worksheet)

    Dim lastRow As Long
    Dim i As Long
    Dim isRowEmpty As Boolean
    Dim countryColumnRange As Range
    Dim selectedCountry As String
    Dim startCol As Long, endCol As Long

    ' Get the selected country from the ComboBox
    selectedCountry = Me.cmbOptions.Value

    ' Find the last row in the report sheet
    lastRow = wsReport.Cells(wsReport.Rows.Count, 1).End(xlUp).Row

    ' Set the column range based on selected country
    Select Case selectedCountry
        Case "UAE"
            startCol = 5 ' E column
            endCol = 8   ' H column
        Case "Hong Kong"
            startCol = 9 ' I column
            endCol = 9   ' I column
        Case "India"
            startCol = 10 ' J column
            endCol = 11   ' K column
        Case "Kuwait"
            startCol = 12 ' L column
            endCol = 12   ' L column
        Case "Qatar"
            startCol = 13 ' M column
            endCol = 13   ' M column
        Case "Oman"
            startCol = 14 ' N column
            endCol = 15   ' O column
        Case "Bahrain"
            startCol = 16 ' P column
            endCol = 17   ' Q column
        Case "Pakistan"
            startCol = 18 ' R column
            endCol = 19   ' S column
        Case "USA"
            startCol = 20 ' T column
            endCol = 21   ' U column
        Case Else
            MsgBox "Country not recognized!", vbExclamation
            Exit Sub
    End Select

    ' Loop through rows from bottom to top
    For i = lastRow To 4 Step -1 ' Data starts from row 4
        isRowEmpty = True ' Assume the row is empty unless we find data

        ' Check the range for the selected country and if it has any non-blank cell
        Set countryColumnRange = wsReport.Range(wsReport.Cells(i, startCol), wsReport.Cells(i, endCol))

        ' If there's any data in the selected country columns, mark the row as not empty
        If WorksheetFunction.CountA(countryColumnRange) > 0 Then
            isRowEmpty = False
        End If

        ' Now, check if all country-specific columns are empty, then delete
        ' But keep A:D and V:BB unaffected
        If isRowEmpty Then
            ' If country columns are empty, check columns A:D and V:BB
            If WorksheetFunction.CountA(wsReport.Range(wsReport.Cells(i, 1), wsReport.Cells(i, 4))) = 0 And _
               WorksheetFunction.CountA(wsReport.Range(wsReport.Cells(i, 22), wsReport.Cells(i, 54))) = 0 Then
                ' If all columns A:D, V:BB, and country columns are empty, delete the row
                wsReport.Rows(i).Delete
            End If
        End If
    Next i

End Sub