| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB code for selecting all rows below filled column
Hi. I need to delete data in all the rows below the last filled row for
column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Kelli,
Range(Range("B65536").End(xlUp)(2), Range("B65536")).EntireRow.Delete HTH, Bernie MS Excel MVP "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Hi KelliInCali
Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left
with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Hi Bernie,
Thanks... Can you tell me what Dims and Sets I would need with this? It doesn't seem to do anything. See my expl to Ron if it helps give an idea of what I'm doing. Here is macro I've recorded to convert TabDel data to columns and then sort by B. From here I do manually what I expl to Ron. Rows("1:3").Select Selection.Delete Shift:=xlUp Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(11, 1), Array(13, 1), Array(29, 1), _ Array(50, 1), Array(63, 1), Array(69, 1), Array(74, 1), Array(75, 1), Array(83, 1), Array( _ 90, 1), Array(91, 1), Array(101, 1), Array(108, 1)), TrailingMinusNumbers:=True Cells.Select Selection.Sort Key1:=Range("B1"), Order1:=xlDescending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal Rows("1:1").Select Selection.Delete Shift:=xlUp Range("B1").Select Range(Selection, Selection.End(xlDown)).Select "Bernie Deitrick" wrote: Kelli, Range(Range("B65536").End(xlUp)(2), Range("B65536")).EntireRow.Delete HTH, Bernie MS Excel MVP "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
The code find the last cell with data in B and add one row
I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
A quick check of next cell '=IF(B588="","empty","not")' returns "not empty"
?? "Ron de Bruin" wrote: The code find the last cell with data in B and add one row I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Hi kelli
If there is a space in the cell the cell is not empty You can select the range that you think is empty and push the delete button Then try the macro again -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... A quick check of next cell '=IF(B588="","empty","not")' returns "not empty" ?? "Ron de Bruin" wrote: The code find the last cell with data in B and add one row I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Ok... when I select the range manually and delete it, the code works. But
the selection and delete is what I am wanting the code to do in the first place. Is there a way to make it work when B is not totally blank? kelli "Ron de Bruin" wrote: Hi kelli If there is a space in the cell the cell is not empty You can select the range that you think is empty and push the delete button Then try the macro again -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... A quick check of next cell '=IF(B588="","empty","not")' returns "not empty" ?? "Ron de Bruin" wrote: The code find the last cell with data in B and add one row I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Hi kelli
Do you import the data ? See what is in the cels first With code we can replace it with nothing Check out http://www.cpearson.com/excel/CellView.htm -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ok... when I select the range manually and delete it, the code works. But the selection and delete is what I am wanting the code to do in the first place. Is there a way to make it work when B is not totally blank? kelli "Ron de Bruin" wrote: Hi kelli If there is a space in the cell the cell is not empty You can select the range that you think is empty and push the delete button Then try the macro again -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... A quick check of next cell '=IF(B588="","empty","not")' returns "not empty" ?? "Ron de Bruin" wrote: The code find the last cell with data in B and add one row I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Ron, Actually it works now! I may have been starting from the wrong place
before. Thanks very much for the help! One other question though, is it possible to command one macro to start from within another? Kelli "Ron de Bruin" wrote: Hi kelli Do you import the data ? See what is in the cels first With code we can replace it with nothing Check out http://www.cpearson.com/excel/CellView.htm -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ok... when I select the range manually and delete it, the code works. But the selection and delete is what I am wanting the code to do in the first place. Is there a way to make it work when B is not totally blank? kelli "Ron de Bruin" wrote: Hi kelli If there is a space in the cell the cell is not empty You can select the range that you think is empty and push the delete button Then try the macro again -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... A quick check of next cell '=IF(B588="","empty","not")' returns "not empty" ?? "Ron de Bruin" wrote: The code find the last cell with data in B and add one row I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Use
Call OtherMacroName In the code of your macro -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron, Actually it works now! I may have been starting from the wrong place before. Thanks very much for the help! One other question though, is it possible to command one macro to start from within another? Kelli "Ron de Bruin" wrote: Hi kelli Do you import the data ? See what is in the cels first With code we can replace it with nothing Check out http://www.cpearson.com/excel/CellView.htm -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ok... when I select the range manually and delete it, the code works. But the selection and delete is what I am wanting the code to do in the first place. Is there a way to make it work when B is not totally blank? kelli "Ron de Bruin" wrote: Hi kelli If there is a space in the cell the cell is not empty You can select the range that you think is empty and push the delete button Then try the macro again -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... A quick check of next cell '=IF(B588="","empty","not")' returns "not empty" ?? "Ron de Bruin" wrote: The code find the last cell with data in B and add one row I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
VB code for selecting all rows below filled column
Ron... Thankyou Thankyou Thankyou Thankyou Thankyou!!! I am now down to just
a few clicks for this entire procedure! You don't know how much repetition you have saved me. Whew... is it Friday yet? -kelli "Ron de Bruin" wrote: Use Call OtherMacroName In the code of your macro -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron, Actually it works now! I may have been starting from the wrong place before. Thanks very much for the help! One other question though, is it possible to command one macro to start from within another? Kelli "Ron de Bruin" wrote: Hi kelli Do you import the data ? See what is in the cels first With code we can replace it with nothing Check out http://www.cpearson.com/excel/CellView.htm -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ok... when I select the range manually and delete it, the code works. But the selection and delete is what I am wanting the code to do in the first place. Is there a way to make it work when B is not totally blank? kelli "Ron de Bruin" wrote: Hi kelli If there is a space in the cell the cell is not empty You can select the range that you think is empty and push the delete button Then try the macro again -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... A quick check of next cell '=IF(B588="","empty","not")' returns "not empty" ?? "Ron de Bruin" wrote: The code find the last cell with data in B and add one row I think your cells are not empty ? -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Ron... Thanks, but doesn't seem to do the trick. When I sort B, I am left with random data in various cols in rows below last data in B. So I need to delete all rows after last "B" data. This doesn't seem to work for that. Manually, I just do Ctrl/Shift/Dwn "B" to find last row, then select very next row and Ctrl/Shft/Dwn again to select from there to 65536 and then delete. But I cannot figure out how to duplicate that in macro. -kelli "Ron de Bruin" wrote: Hi KelliInCali Try this Dim lr As Long lr = Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Row Range("A" & lr & ":IV" & Rows.Count).Delete -- Regards Ron de Bruin http://www.rondebruin.nl "KelliInCali" wrote in message ... Hi. I need to delete data in all the rows below the last filled row for column B. Can anyone give me code for that? Thanks in advance, Kelli |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Delete entire rows by selecting different text in column A | Excel Discussion (Misc queries) | |||
| Formula selecting first 1400 rows of column, need ALL of it. HELP | Excel Worksheet Functions | |||
| after selecting 50 rows of a column i can't reference the cells in the rows | New Users to Excel | |||
| Selecting Rows by Column Value | Excel Programming | |||
| VBA code for selecting and copying rows to another sheet. | Excel Programming | |||
Linear Mode
