Clear Contents below a dynamic Range

Keewi82

New Member
Joined
Jun 29, 2016
Messages
9
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.
ab
11011111500
21011157500
3
1010257500
4500
5500
6500
7500

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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.
ab
11011111500
21011157500
3
1010257500
4500
5500
6500
7500

<tbody>
</tbody>

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

New Member
Joined
Jun 29, 2016
Messages
9
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

Active Member
Joined
Nov 18, 2006
Messages
289
Something like this?


Code:
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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,334
Office Version
  1. 2010
Platform
  1. Windows
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.
Code:
[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]
Code:
[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]
 

Watch MrExcel Video

Forum statistics

Threads
1,109,446
Messages
5,528,802
Members
409,836
Latest member
karnasrinivas
Top