Delete variable range when cell is emtpy
 

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

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

Delete variable range when cell is emtpy
 

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?

"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

Delete variable range when cell is emtpy
 

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

Delete variable range when cell is emtpy
 

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

Delete variable range when cell is emtpy
 

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

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

Delete variable range when cell is emtpy
 

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

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

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

Delete variable range when cell is emtpy
 

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

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

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

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

Delete variable range when cell is emtpy
 

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