Clear contents of cells based on the values of adjacent cells

[davefaust78]

Hi,

I am writing a VBA code to automatically clean up some excel reports I run on a regular basis and I have a situation where I am stuck.

Column J of my data contains Country Names and column K of my data has State Names.
Here's what I need:
If the values in column J are not equal to either United States or Canada, then clear the states value name in the adjacent cell.

For example, Here's how my data currently looks:
[TABLE="class: grid, width: 200, align: left"]
<tbody>[TR]
[TD]Canada[/TD]
[TD]BC[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]TX[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Rio de Janeiro[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]NY
[/TD]
[/TR]
[TR]
[TD]Canada[/TD]
[TD]SK[/TD]
[/TR]
[TR]
[TD]Chile[/TD]
[TD]Santiago[/TD]
[/TR]
[TR]
[TD]Brazil[/TD]
[TD]Goias[/TD]
[/TR]
[TR]
[TD]Italy[/TD]
[TD]Roma[/TD]
[/TR]
[TR]
[TD]United States[/TD]
[TD]CA[/TD]
[/TR]
[TR]
[TD]United Kingdom[/TD]
[TD]Surry[/TD]
[/TR]
</tbody>[/TABLE]


















For each of the countries that are not equal to United States or Canada, I need the state values cleared.

It's driving me crazy as I'm relatively new to VBA. Any help that anyone can offer would be a huge lifesaver.
I'd like to run this as it's own sub.

Thank you.
Regards,
Dave

 

[Joe4]

Welcome to the Board!

Here is one way:

Sub MyCleanData()

    Dim lastRow As Long
    Dim myRow As Long
    
    Application.ScreenUpdating = False
   
'   Find last row with data in column K
    lastRow = Cells(Rows.Count, "K").End(xlUp).Row
    
'   Loop through all rows, starting on row 2
    For myRow = 2 To lastRow
        If Cells(myRow, "J") <> "United States" And Cells(myRow, "J") <> "Canada" Then
            Cells(myRow, "K").ClearContents
        End If
    Next myRow
    
    Application.ScreenUpdating = True

End Sub

 

[Fluff]

Hi & welcome to MrExcel
Try

Sub DelStates()
' davefaust78

    Dim Cl As Range
    
    For Each Cl In Range("J1", Range("J" & Rows.Count).End(xlUp))
        If Not Cl.Value = "Canada" And Not Cl.Value = "United States" Then
            Cl.Offset(, 1).ClearContents
        End If
    Next Cl
    
End Sub

 

[davefaust78]

Hi Joe4,

Thank you so much for your quick reply. This is exactly what I needed and it works perfectly!

Regards,
Dave

 

[Joe4]

You are welcome!

 

[Rick Rothstein]

Here is another macro for you to try...

[table="width: 500"]
[tr]
	[td]Sub ClearNonCanadaNonUSstates()
  Dim LastRow As Long
  LastRow = Cells(Rows.Count, "J").End(xlUp).Row
  Range("K1:K" & LastRow) = Evaluate(Replace("=IF((J1:J#=""United States"")+(J1:J#=""Canada""),K1:K#,"""")", "#", LastRow))
End Sub[/td]
[/tr]
[/table]