Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recognize first emtpy Cell in column | Excel Programming | |||
emtpy cell | Excel Programming | |||
Delete all Rows in a Variable Range | Excel Programming | |||
insert amount into first emtpy cell | Excel Programming | |||
Hide row when cell is emtpy | Excel Programming |