Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete last line of numbers
Hi...
I import a block of text from another sheet with a simple macro but want to delete the last line that holds numerical values. The block of data varies in depth and the line to removed can vary say from line 20 - 8000. I need some code that will identify when the numercial data stops and to delete the line on which the data appears last. Anything out there? Cheers Gordon. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete last line of numbers
Where is this block of text at... In a simple String variable within your
code? In a String array within your code? In a single cell in your worksheet? Or split up into rows (by lines) in your worksheet? Your use of the word "import" seems to suggest it is in the worksheet somewhere, but your use of the word "lines" instead of "rows" seems to suggest it is in a variable somewhere. Also, you said "the last line that holds numerical values" which suggests this "line" has more than one value in it... can you post a sample of what that line (or row if in a worksheet) would look like (I'm interested in how the multiple values are delimited)? Rick "Gordon" wrote in message ... Hi... I import a block of text from another sheet with a simple macro but want to delete the last line that holds numerical values. The block of data varies in depth and the line to removed can vary say from line 20 - 8000. I need some code that will identify when the numercial data stops and to delete the line on which the data appears last. Anything out there? Cheers Gordon. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete last line of numbers
Assume that column 1 can be used to make the determination.
Dim r as Range, r1 as Range, r2 as Range ' get all cells in the column that contain numbers set r = columns(1).specialcells(xlconstants,xlnumbers) get the last set of cells that contain numbers set r1 = r.areas(r.areas.count) get the last cell in the last set of cells that contain numbers set r2 = r1(r1.count) msgbox r2.address -- Regards, Tom Ogilvy "Gordon" wrote: Hi... I import a block of text from another sheet with a simple macro but want to delete the last line that holds numerical values. The block of data varies in depth and the line to removed can vary say from line 20 - 8000. I need some code that will identify when the numercial data stops and to delete the line on which the data appears last. Anything out there? Cheers Gordon. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete last line of numbers
Hi Rick...
Apologies for mixed terminology. It's much simpler than that. A block of data is dumped in at cell A6 and goes across to column U. The data that goes down however can go from row 6 to row 8000. The code needs to idnetify the last line (not a gap) and then delete the last line, and nbot identify the new last line. Make sense! G "Rick Rothstein (MVP - VB)" wrote: Where is this block of text at... In a simple String variable within your code? In a String array within your code? In a single cell in your worksheet? Or split up into rows (by lines) in your worksheet? Your use of the word "import" seems to suggest it is in the worksheet somewhere, but your use of the word "lines" instead of "rows" seems to suggest it is in a variable somewhere. Also, you said "the last line that holds numerical values" which suggests this "line" has more than one value in it... can you post a sample of what that line (or row if in a worksheet) would look like (I'm interested in how the multiple values are delimited)? Rick "Gordon" wrote in message ... Hi... I import a block of text from another sheet with a simple macro but want to delete the last line that holds numerical values. The block of data varies in depth and the line to removed can vary say from line 20 - 8000. I need some code that will identify when the numercial data stops and to delete the line on which the data appears last. Anything out there? Cheers Gordon. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete last line of numbers
Assuming Column A always has an entry in it for each data line, you can
identify the last line of data like this... LastDataRow = Cells(Rows.Count, "A").End(xlUp).Row Although you should probably qualify the Cells property with a reference to the worksheet it is on. I'm not 100% sure this is what you actually want, though, as your initial post seemed to indicate you had regular text mixed with numerical text and the above only find the last line no matter what type of data is in it. Rick "Gordon" wrote in message ... Hi Rick... Apologies for mixed terminology. It's much simpler than that. A block of data is dumped in at cell A6 and goes across to column U. The data that goes down however can go from row 6 to row 8000. The code needs to idnetify the last line (not a gap) and then delete the last line, and nbot identify the new last line. Make sense! G "Rick Rothstein (MVP - VB)" wrote: Where is this block of text at... In a simple String variable within your code? In a String array within your code? In a single cell in your worksheet? Or split up into rows (by lines) in your worksheet? Your use of the word "import" seems to suggest it is in the worksheet somewhere, but your use of the word "lines" instead of "rows" seems to suggest it is in a variable somewhere. Also, you said "the last line that holds numerical values" which suggests this "line" has more than one value in it... can you post a sample of what that line (or row if in a worksheet) would look like (I'm interested in how the multiple values are delimited)? Rick "Gordon" wrote in message ... Hi... I import a block of text from another sheet with a simple macro but want to delete the last line that holds numerical values. The block of data varies in depth and the line to removed can vary say from line 20 - 8000. I need some code that will identify when the numercial data stops and to delete the line on which the data appears last. Anything out there? Cheers Gordon. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
delete last line of numbers
This auumes the block includes column A. It starts working backwards and
deletes the FIRST numeric row it finds (working backwards): Sub flash() n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 1 Step -1 If IsNumeric(Cells(i, "A").Value) Then Cells(i, "A").EntireRow.Delete Exit Sub End If Next End Sub -- Gary''s Student - gsnu200794 "Gordon" wrote: Hi... I import a block of text from another sheet with a simple macro but want to delete the last line that holds numerical values. The block of data varies in depth and the line to removed can vary say from line 20 - 8000. I need some code that will identify when the numercial data stops and to delete the line on which the data appears last. Anything out there? Cheers Gordon. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Delete all negative numbers leaving only positive numbers | Excel Discussion (Misc queries) | |||
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Worksheet Functions | |||
Delete all but first line | Excel Discussion (Misc queries) | |||
Line numbers some numbers are blue some are black | Excel Discussion (Misc queries) | |||
how to delete to the end of line | Excel Programming |