Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Deletes Top to bottom
Hi Guys, I am trying to delete all the rows preceeding the FIRST ro that has 'o1CR' in the first 4 bytes of col 'A'. The very first ro contains a hdr with the literal "AccountNumber" in cell A1. I have managed to locate the 01CR rec and realize the correct ro number to start my deletes moving from bottom (the 01CR rec ) to th top (which I am told is the best way to delete in Excel). However I have not been successful in assigning the row that proced the 01CR row as the LastRow. Does anyone know how I do that? Here is my code. I know there are better techniques but Excel is not m first language. Sub CleanUp_File_2() Dim i As Integer Dim MaxElements As Integer Dim cnt As Integer Dim LeftValue As String Dim HDR As Range Dim row1 As Variant LeftValue = "" cnt = 0 MaxElements = 7000 ActiveCell.Range("A2").Select For i = 1 To MaxElements LeftValue = Left(Cells(i, 1), 11) If Left(Cells(i, 1), 4) = "01CR" Then Exit For Else cnt = cnt + 1 End If If cnt = 4379 Then Stop End If Next i Range("A2").Select ActiveCell.FormulaR1C1 = "HDR" Dim LastRow As Range cnt = cnt - 1 row1 = ("A" & cnt) LastRow = Worksheets("sheet1").Cells(cnt, 0) For i = LastRow To HDR Step 1 - 1 If Cells(i, 0) = "AccountNumber" Then Else Rows(i).Delete End If Next i End Sub The error msg appication defines or object defined error becaus .Cells(cnt, 0) is invalid. How can I fix this -- Trudy ----------------------------------------------------------------------- Trudye's Profile: http://www.excelforum.com/member.php...fo&userid=3249 View this thread: http://www.excelforum.com/showthread.php?threadid=52287 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Deletes Top to bottom
So you want to keep row 1 and delete rows 2 to the first cell that contains 01CR
as the first 4 characters, but keep that 01CR row? Option Explicit Sub testme() Dim FoundCell As Range Dim WhatToFind As String WhatToFind = "01CR*" With Worksheets("sheet1") With .Range("a:a") Set FoundCell = .Cells.Find(what:=WhatToFind, _ after:=.Cells(.Cells.Count), _ LookIn:=xlValues, _ lookat:=xlWhole, _ searchorder:=xlByRows, _ searchdirection:=xlNext, _ MatchCase:=False) If FoundCell Is Nothing Then MsgBox WhatToFind & " wasn't found" Else If FoundCell.Row < 3 Then 'do nothing, it's already cleaned up Else .Range("a2:A" & FoundCell.Row - 1).EntireRow.Delete End If End If End With End With End Sub It's essentially going to the bottom of column A and doing Edit|find and looking for the topmost cell with 01CR in it. Then deleting all the rows from 2 to the one above it. Trudye wrote: Hi Guys, I am trying to delete all the rows preceeding the FIRST row that has 'o1CR' in the first 4 bytes of col 'A'. The very first row contains a hdr with the literal "AccountNumber" in cell A1. I have managed to locate the 01CR rec and realize the correct row number to start my deletes moving from bottom (the 01CR rec ) to the top (which I am told is the best way to delete in Excel). However I have not been successful in assigning the row that proceds the 01CR row as the LastRow. Does anyone know how I do that? Here is my code. I know there are better techniques but Excel is not my first language. Sub CleanUp_File_2() Dim i As Integer Dim MaxElements As Integer Dim cnt As Integer Dim LeftValue As String Dim HDR As Range Dim row1 As Variant LeftValue = "" cnt = 0 MaxElements = 7000 ActiveCell.Range("A2").Select For i = 1 To MaxElements LeftValue = Left(Cells(i, 1), 11) If Left(Cells(i, 1), 4) = "01CR" Then Exit For Else cnt = cnt + 1 End If If cnt = 4379 Then Stop End If Next i Range("A2").Select ActiveCell.FormulaR1C1 = "HDR" Dim LastRow As Range cnt = cnt - 1 row1 = ("A" & cnt) LastRow = Worksheets("sheet1").Cells(cnt, 0) For i = LastRow To HDR Step 1 - 1 If Cells(i, 0) = "AccountNumber" Then Else Rows(i).Delete End If Next i End Sub The error msg appication defines or object defined error because Cells(cnt, 0) is invalid. How can I fix this. -- Trudye ------------------------------------------------------------------------ Trudye's Profile: http://www.excelforum.com/member.php...o&userid=32496 View this thread: http://www.excelforum.com/showthread...hreadid=522877 -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Deletes Top to bottom
Just wanted to offer one or two pointers on your code (for future reference -
my solution is similar to Dave's so I won't repeat what's already done) LastRow = Worksheets("sheet1").Cells(cnt, 0) won't work because there is no column 0. Also, you declared Lastrow as a range, so you must use a Set statement. Set LastRow = Worksheets("sheet1").Cells(cnt, 1) Once you know the first and last rows you can delete the entire range without looping backwards (which I personally hate because it is much slower to delete one row at a time). One last thing - I wasn't clear on if you wanted to delete the entire row or not. If you don't you should be able to change this line in Dave's macro from: ..Range("a2:A" & FoundCell.Row - 1).EntireRow.Delete To ..Range("a2:A" & FoundCell.Row - 1).Rows.Delete (xlup) "Trudye" wrote: Hi Guys, I am trying to delete all the rows preceeding the FIRST row that has 'o1CR' in the first 4 bytes of col 'A'. The very first row contains a hdr with the literal "AccountNumber" in cell A1. I have managed to locate the 01CR rec and realize the correct row number to start my deletes moving from bottom (the 01CR rec ) to the top (which I am told is the best way to delete in Excel). However I have not been successful in assigning the row that proceds the 01CR row as the LastRow. Does anyone know how I do that? Here is my code. I know there are better techniques but Excel is not my first language. Sub CleanUp_File_2() Dim i As Integer Dim MaxElements As Integer Dim cnt As Integer Dim LeftValue As String Dim HDR As Range Dim row1 As Variant LeftValue = "" cnt = 0 MaxElements = 7000 ActiveCell.Range("A2").Select For i = 1 To MaxElements LeftValue = Left(Cells(i, 1), 11) If Left(Cells(i, 1), 4) = "01CR" Then Exit For Else cnt = cnt + 1 End If If cnt = 4379 Then Stop End If Next i Range("A2").Select ActiveCell.FormulaR1C1 = "HDR" Dim LastRow As Range cnt = cnt - 1 row1 = ("A" & cnt) LastRow = Worksheets("sheet1").Cells(cnt, 0) For i = LastRow To HDR Step 1 - 1 If Cells(i, 0) = "AccountNumber" Then Else Rows(i).Delete End If Next i End Sub The error msg appication defines or object defined error because .Cells(cnt, 0) is invalid. How can I fix this. -- Trudye ------------------------------------------------------------------------ Trudye's Profile: http://www.excelforum.com/member.php...o&userid=32496 View this thread: http://www.excelforum.com/showthread...hreadid=522877 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel deletes and resets Recent Documents | Excel Discussion (Misc queries) | |||
Excel Charting Macro That deletes Sheet | Excel Discussion (Misc queries) | |||
Excel Charting Macro, that deletes previous sheet | Excel Discussion (Misc queries) | |||
Detecting when Excel user inserts or deletes a row | Excel Programming | |||
hitting spacebar in excel deletes last character entered. | New Users to Excel |