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.

--
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