Untitled

Private Sub FilterAndRemoveBlankRows(wsReport As Worksheet)
    Dim lastRow As Long
    Dim i As Long
    Dim countryColumnRange As Range
    Dim isRowEmpty As Boolean
    Dim selectedCountry As String
    Dim countryColumns As Variant
    
    ' Get the selected country from the dropdown
    selectedCountry = Me.cmbOptions.Value ' Assuming cmbOptions contains the selected country
    
    ' Define country-specific column ranges based on the selected country
    If selectedCountry = "UAE" Then
        countryColumns = Array(5, 6, 7, 8) ' Columns E:H for UAE
    ElseIf selectedCountry = "Hong Kong" Then
        countryColumns = Array(9, 9) ' Column I for Hong Kong
    ElseIf selectedCountry = "India" Then
        countryColumns = Array(10, 11) ' Columns J:K for India
    ElseIf selectedCountry = "Kuwait" Then
        countryColumns = Array(12, 12) ' Column L for Kuwait
    ElseIf selectedCountry = "Qatar" Then
        countryColumns = Array(13, 13) ' Column M for Qatar
    ElseIf selectedCountry = "Oman" Then
        countryColumns = Array(14, 15) ' Columns N:O for Oman
    ElseIf selectedCountry = "Bahrain" Then
        countryColumns = Array(16, 17) ' Columns P:Q for Bahrain
    ElseIf selectedCountry = "Pakistan" Then
        countryColumns = Array(18, 19) ' Columns R:S for Pakistan
    ElseIf selectedCountry = "USA" Then
        countryColumns = Array(20, 21) ' Columns T:U for USA
    End If

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

    ' Loop through each row from the bottom to the top
    For i = lastRow To 4 Step -1 ' Start from row 4 (assuming header is in row 3)
        isRowEmpty = True ' Assume row is empty unless proven otherwise
        
        ' Check all columns for the selected country
        For Each col In countryColumns
            Set countryColumnRange = wsReport.Range(wsReport.Cells(i, col), wsReport.Cells(i, col))
            If WorksheetFunction.CountA(countryColumnRange) > 0 Then
                isRowEmpty = False ' If any country column has content, keep the row
                Exit For
            End If
        Next col
        
        ' If all country columns are empty, delete the row
        If isRowEmpty Then
            wsReport.Rows(i).Delete
        End If
    Next i
End Sub