Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
MULTIPLY CELLS IN ROWS CONTINUALLY ALY33 New Users to Excel 1 May 3rd 07 05:06 PM
rows identification and deletion TUNGANA KURMA RAJU Excel Discussion (Misc queries) 2 April 2nd 07 04:42 AM
On deletion of rows Stephen Howe Excel Discussion (Misc queries) 2 October 7th 05 06:02 PM
how to repeat rows at the bottom while using the rows repeat at to Rows to repeat at the bottom Setting up and Configuration of Excel 1 August 31st 05 02:06 PM
Title Cut Off if Rows to Repeat & Columns to Repeat are Both Selec unibaby Excel Discussion (Misc queries) 2 August 24th 05 04:29 PM


All times are GMT +1. The time now is 07:54 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"