Clear Contents below a dynamic Range

[Keewi82]

Hi everyone

hopefully this is a simple one but my limited VBA experience has me stumped. I want to clear the contents of columns a and b when a is blank. Below is the table of sample data i want to clear cells a4:b7, however this range need to be dynamic based off if column A is blank and b contains something. [TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1011111[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1011157[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1010257[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Rick Rothstein]

Hi everyone

hopefully this is a simple one but my limited VBA experience has me stumped. I want to clear the contents of columns a and b when a is blank. Below is the table of sample data i want to clear cells a4:b7, however this range need to be dynamic based off if column A is blank and b contains something. [TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]a[/TD]
[TD]b[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1011111[/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1011157[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1010257[/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]500
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]500[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

What is in Column A that needs to be cleared... formulas displaying the empty text string ("")?

Will the blanks always be located after the last cell in Column A displaying a number?

 

[Keewi82]

Hi Rick
I probably wasn't as clear as i should have been. It column B that need be be cleared if a is blank. Column A is not formula driven.

 

[NicholasP]

Something like this?

Sub test()

Dim lastrw As Long
lastrw = Cells(Rows.Count, 2).End(xlUp).Row


For Each cell In Range("A1:A" & lastrw)
    If IsEmpty(cell) = True Then
        cell.Offset(0, 1).ClearContents
    End If
Next
    

End Sub

Note that the lastrw variable is determining the extent of the range of cells to be examined. Also, lastrw is based on Column B.

 

[Rick Rothstein]

Hi Rick
I probably wasn't as clear as i should have been. It column B that need be be cleared if a is blank. Column A is not formula driven.

You did not answer my second question, so here are two macros... the first should be used if all the blank cells always occur after the last data cell in Column A (like you example shows) and the second should be used if blank cells could occur in between data cells in Column A.

[table="width: 500"]
[tr]
	[td]Sub DeleteIfBlanksAtEnd()
  Range(Cells(Rows.Count, "A").End(xlUp).Offset(1, 1), Cells(Rows.Count, "B").End(xlUp)).ClearContents
End Sub[/td]
[/tr]
[/table]

[table="width: 500"]
[tr]
	[td]Sub DeleteIfBlanksScatteredAbout()
  Range("A1", Cells(Rows.Count, "B").End(xlUp).Offset(, -1)).SpecialCells(xlBlanks).Offset(, 1).ClearContents
End Sub[/td]
[/tr]
[/table]

 

[Keewi82]

Top one is perfect Thanks Rick