| 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 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 |
|
#4
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 |
|
#5
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 |
|
#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 |
| 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 | |||
Hybrid Mode
