Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shift Rows Up After Row Deletion and Continually Repeat
Hello! I would greatly appreciate your expertise on opening up a text file
and deleting rows of data if they do not meet the format of "0.0", "1.0", "2.0", etc. After that delete any empty rows and shift the rows up to form a consecutive numbering sequence in column A. The order of operations should not matter as long as it is efficient, the total number of rows I'm working with is ~7K. Your time and expertise is greatly appreciated. Kind regards, D.Parker |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shift Rows Up After Row Deletion and Continually Repeat
Sub Delete_By_Criteria()
Dim i As Integer Dim iLastRow As Integer Dim Collet As String Dim whatwant As String Set wks = ActiveSheet Application.ScreenUpdating = False iLastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row For i = iLastRow To 1 Step -1 If Not wks.Cells(i, 1).Value Like "*.0" Then wks.Rows(i).Delete End If Next i Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 9 Oct 2007 14:10:01 -0700, D.Parker wrote: Hello! I would greatly appreciate your expertise on opening up a text file and deleting rows of data if they do not meet the format of "0.0", "1.0", "2.0", etc. After that delete any empty rows and shift the rows up to form a consecutive numbering sequence in column A. The order of operations should not matter as long as it is efficient, the total number of rows I'm working with is ~7K. Your time and expertise is greatly appreciated. Kind regards, D.Parker |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shift Rows Up After Row Deletion and Continually Repeat
I ran this and it deleted "everything" on my sheet. I was looking for the
following: 1.0 2.0 3.0 4.0 and so on. Can you please re-advise? Kind regards, D.Parker "Gord Dibben" wrote: Sub Delete_By_Criteria() Dim i As Integer Dim iLastRow As Integer Dim Collet As String Dim whatwant As String Set wks = ActiveSheet Application.ScreenUpdating = False iLastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row For i = iLastRow To 1 Step -1 If Not wks.Cells(i, 1).Value Like "*.0" Then wks.Rows(i).Delete End If Next i Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 9 Oct 2007 14:10:01 -0700, D.Parker wrote: Hello! I would greatly appreciate your expertise on opening up a text file and deleting rows of data if they do not meet the format of "0.0", "1.0", "2.0", etc. After that delete any empty rows and shift the rows up to form a consecutive numbering sequence in column A. The order of operations should not matter as long as it is efficient, the total number of rows I'm working with is ~7K. Your time and expertise is greatly appreciated. Kind regards, D.Parker |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shift Rows Up After Row Deletion and Continually Repeat
Is the data text or numeric?
You originally stated you opened a text file so I assumed the data was text. The macro looks for text data and deletes any rows that do not contain *.0 When you open the *.txt file and the wizard pops, hit NextNextColumn Data Format. Check "Text" and Finish. Now the macro should work. Gord On Tue, 9 Oct 2007 19:15:04 -0700, D.Parker wrote: I ran this and it deleted "everything" on my sheet. I was looking for the following: 1.0 2.0 3.0 4.0 and so on. Can you please re-advise? Kind regards, D.Parker "Gord Dibben" wrote: Sub Delete_By_Criteria() Dim i As Integer Dim iLastRow As Integer Dim Collet As String Dim whatwant As String Set wks = ActiveSheet Application.ScreenUpdating = False iLastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row For i = iLastRow To 1 Step -1 If Not wks.Cells(i, 1).Value Like "*.0" Then wks.Rows(i).Delete End If Next i Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 9 Oct 2007 14:10:01 -0700, D.Parker wrote: Hello! I would greatly appreciate your expertise on opening up a text file and deleting rows of data if they do not meet the format of "0.0", "1.0", "2.0", etc. After that delete any empty rows and shift the rows up to form a consecutive numbering sequence in column A. The order of operations should not matter as long as it is efficient, the total number of rows I'm working with is ~7K. Your time and expertise is greatly appreciated. Kind regards, D.Parker |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Shift Rows Up After Row Deletion and Continually Repeat
Gord, I tried this and the same result when I ran the code. Everything on my
sheet was deleted, event my command button. Just to clarify, I am opening a text file that is space, comma delimited and thus the numbers in the column appear in the form 0.0000, 0.345, 1.2345, 2.987...and so on. I format the column with the numbers to only 1 decimal place such that I can purge out any data that does not appear in the form 0.0, 1.0, 2.0, 3.0 and so on. After this, if I have duplicate rows of 0.0, 0.0, 1.0, 1.0, 2.0, 2.0 and so on one row from each interger must be deleted so I end up with 0.0, 1.0, 2.0, 3.0, etc. I hope this clears up any ambiguity. I'm still puzzled why everything on the sheet gets deleted. Kind regards, D.Parker "Gord Dibben" wrote: Is the data text or numeric? You originally stated you opened a text file so I assumed the data was text. The macro looks for text data and deletes any rows that do not contain *.0 When you open the *.txt file and the wizard pops, hit NextNextColumn Data Format. Check "Text" and Finish. Now the macro should work. Gord On Tue, 9 Oct 2007 19:15:04 -0700, D.Parker wrote: I ran this and it deleted "everything" on my sheet. I was looking for the following: 1.0 2.0 3.0 4.0 and so on. Can you please re-advise? Kind regards, D.Parker "Gord Dibben" wrote: Sub Delete_By_Criteria() Dim i As Integer Dim iLastRow As Integer Dim Collet As String Dim whatwant As String Set wks = ActiveSheet Application.ScreenUpdating = False iLastRow = wks.Cells(Rows.Count, 1).End(xlUp).Row For i = iLastRow To 1 Step -1 If Not wks.Cells(i, 1).Value Like "*.0" Then wks.Rows(i).Delete End If Next i Application.ScreenUpdating = True End Sub Gord Dibben MS Excel MVP On Tue, 9 Oct 2007 14:10:01 -0700, D.Parker wrote: Hello! I would greatly appreciate your expertise on opening up a text file and deleting rows of data if they do not meet the format of "0.0", "1.0", "2.0", etc. After that delete any empty rows and shift the rows up to form a consecutive numbering sequence in column A. The order of operations should not matter as long as it is efficient, the total number of rows I'm working with is ~7K. Your time and expertise is greatly appreciated. Kind regards, D.Parker |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
MULTIPLY CELLS IN ROWS CONTINUALLY | New Users to Excel | |||
rows identification and deletion | Excel Discussion (Misc queries) | |||
On deletion of rows | Excel Discussion (Misc queries) | |||
how to repeat rows at the bottom while using the rows repeat at to | Setting up and Configuration of Excel | |||
Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec | Excel Discussion (Misc queries) |