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 selected or invalid.", vbExclamation
            Exit Sub
    End Select
    
    ' Loop through rows from the bottom to the 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 current 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's column, set isRowEmpty to False
        If WorksheetFunction.CountA(countryColumnRange) > 0 Then
            isRowEmpty = False ' At least one cell has data 
        End If
        
        ' If all the columns for this country are blank, delete the row
        If isRowEmpty Then
            wsReport.Rows(i).Delete
        End If
    Next i

End Sub