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.
--
HTH,
Barb Reinhardt



"James C." wrote:

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" wrote:

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