Delete variable range when cell is emtpy
Try this
Sub Test()
Dim myDeleteRange As Range
Dim myWS As Worksheet
Dim myRange As Range
Dim r As Range
Set myWS = Sheets("Data")
Set myDeleteRange = Nothing
Set myRange = myWS.Cells(7, 43)
lrow = myRange.Parent.Cells(myRange.Parent.Rows.Count,
myRange.Column).End(xlUp).Row
If lrow myRange.Row Then
Set myRange = myRange.Resize(lrow - myRange.Row + 1, 1)
Debug.Print myRange.Address
End If
For Each r In myRange
If IsEmpty(r) Then
If myDeleteRange Is Nothing Then
Set myDeleteRange = r
Else
Set myDeleteRange = Union(myDeleteRange, r)
End If
End If
Next r
If Not myDeleteRange Is Nothing Then
myDeleteRange.Delete Shift:=xlUp
End If
End Sub
--
HTH,
Barb Reinhardt
"James C." wrote:
Hi all,
I am trying to delete a range that will vary depending on how much data is
in a previous column. For instance I have a data tab that has a query on it.
I never know how much data will come from that query but I do know that I
when there is no data at the bottom of the query I would like to delete the
five cells to the right of it down to the bottom of the page. This is what I
have so far, but I don't know how to insert a variable into a range.
x = 7
Do while Sheets("Data").Cells(x, 43) = ""
Range("x, 48").Select -- (Not sure on this part)
Selection.Delete Shift:=xlUp
x = x+1
Loop
|