Delete variable range when cell is emtpy

[James C.]

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

 

[Barb Reinhardt]

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

 

[James C.]

Hi Barb,

I tried this out and it doesn't seem to be working. When i step through it,
it correctly identifies the last row of my query but doesn't do anything
after that. Any ideas?

 

[James C.]

Barb,

Here is what I have so far (with your inclusions).... This gets me to the
row I need to be at. Now I want it to delete columns 44 through 68 from this
row down.

Sub Test()

Dim myWS As Worksheet
Dim myRange As Range

Set myWS = Sheets("Annual Data")
Set myRange = myWS.Cells(7, 43)

lrow = myRange.Parent.Cells(myRange.Parent.Rows.Count,
myRange.Column).End(xlUp).Row
lrow = lrow - 1

End sub

 

[Barb Reinhardt]

I assumed that lRow was the last row of data in the column of interest. What
do you get for lRow when you run it and how many more rows do you want to
use. Are there columns of data adjacent to it in the same row? If so, which
ones.

 

[James C.]

lRow is the first empty row of data. I want to delete the adjacent 24 columns
(which happen to be columns 44 through 68). I want to delete all the way to
the bottom of the page. The columns to be deleted never change, just the
starting point.

 

[Barb Reinhardt]

OK, now I'm confused. In the initial request, I thought you wanted to delete
the cells that had no entry. Now you want to delete the adjacent columns.
Give me some specific examples.

If the last entry is in Row 40, Specifically which cells do you want to
delete?

Thanks,
Barb Reinhardt

 

[James C.]

If the last entry is Row 40 (Column 43 -- which is AQ) then I want to delete
the adjacent Column 44 - 68 from Row 40 down to the bottom

Thanks again for the help

 

[Barb Reinhardt]

Try this

Sub Test()

Dim myWS As Worksheet
Dim myRange As Range

Set myWS = Sheets("Annual Data")
Set myRange = myWS.Cells(7, 43)

Set myRange = myRange.Parent.Cells(myRange.Parent.Rows.Count, _
myRange.Column).End(xlUp)

Set myRange = myRange.Offset(0, 1).Resize(myRange.Parent.Rows.Count -
myRange.Row, 24)

Debug.Print myRange.Address

'I think what you want to do it
myRange.Interior.ColorIndex = 36
'myrange.Delete

End Sub
If the cells highlighted in YELLOW are the ones youwant to delete, take out
the comment. If not, tweak the range definition.