Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
One other note, when I format my column to text I no longer have the
precision of 0.0, 1.0, 2.0, 3.0....n.0. It is in the format of 0,1,2,3,4,.....n. Thank you in advance for your support. Kind regards, D.Parker "D.Parker" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
The code that Gord provided does work, but I had to manually input .0 after
all my values. Is there a way to either have my text read as 0.0, 1.0, etc. once it is formatted OR how would the algorithm be modified to reflect integers with no decimal as the criteria? Sorry for the multiple posts and thank you for your assistance. Kind regards, D.Parker "D.Parker" wrote: One other note, when I format my column to text I no longer have the precision of 0.0, 1.0, 2.0, 3.0....n.0. It is in the format of 0,1,2,3,4,.....n. Thank you in advance for your support. Kind regards, D.Parker "D.Parker" wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
If you want to look at the way the data is formatted in the cell, try changing:
If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Text Like "*.0" Then But I'm confused about the duplicates and the changes in formatting that you've described in the other posts. D.Parker wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
My apologies for any confusion. What happens is the following.
1) I open a .txt file in Excel. 2) The first column of data I format the numbers in the column to 1 decimal place ex. 1.23568 goes to 1.2 1.0235 goes to 1.0 and so on. 3) I have to go back and change the values to "Precision As Displayed" to TRY and retain the text format of 1.0, 1.2, 2.3, 4.0, 3.1, and so on. 4) When I do that the numbers that are 1.0, 2.0, 3.0 etc. are now 1, 2, 3 ....n. 5) When I run the algorithm the everything on my sheet is deleted. 6) I manually manipulated the cells to reflect 1.0, 2.0, 3.0...n and the algorithm work accordingly. Thus, my question was how to you keep the decimal and zero when you change the values to text keeping the Precision As Displayed? Otherwise, I have to embed a .0 after each whole number (i.e. 1, 2, 3, ...) in order to get the format 1.0, 2.0, 3.0. Or can the algorithm be changed to check for whole numbers in text format instead of "*.0"? Whichever method is easiest is preferred. After I manually manipulate the fields and the algorithm runs, the second problem is that I have multiple rows of 1.0, 2.0, 3.0 and I need to purge out the duplicate fields such that I go from a column A displaying 1.0, 1.0, 2.0, 2.0, 3.0, 3.0,...n,n to 1.0, 2.0, 3.0...n. I hope this helps you to help me and thank you for you time. Kind regards, D.Parker "Dave Peterson" wrote: If you want to look at the way the data is formatted in the cell, try changing: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Text Like "*.0" Then But I'm confused about the duplicates and the changes in formatting that you've described in the other posts. D.Parker wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
So if you format the cells as 0.0 and use .text, does the code work ok?
Or you could check to see if the value is a whole number: If int(wks.Cells(i, 1).value) = wks.cells(i,1).value then D.Parker wrote: My apologies for any confusion. What happens is the following. 1) I open a .txt file in Excel. 2) The first column of data I format the numbers in the column to 1 decimal place ex. 1.23568 goes to 1.2 1.0235 goes to 1.0 and so on. 3) I have to go back and change the values to "Precision As Displayed" to TRY and retain the text format of 1.0, 1.2, 2.3, 4.0, 3.1, and so on. 4) When I do that the numbers that are 1.0, 2.0, 3.0 etc. are now 1, 2, 3 ...n. 5) When I run the algorithm the everything on my sheet is deleted. 6) I manually manipulated the cells to reflect 1.0, 2.0, 3.0...n and the algorithm work accordingly. Thus, my question was how to you keep the decimal and zero when you change the values to text keeping the Precision As Displayed? Otherwise, I have to embed a .0 after each whole number (i.e. 1, 2, 3, ...) in order to get the format 1.0, 2.0, 3.0. Or can the algorithm be changed to check for whole numbers in text format instead of "*.0"? Whichever method is easiest is preferred. After I manually manipulate the fields and the algorithm runs, the second problem is that I have multiple rows of 1.0, 2.0, 3.0 and I need to purge out the duplicate fields such that I go from a column A displaying 1.0, 1.0, 2.0, 2.0, 3.0, 3.0,...n,n to 1.0, 2.0, 3.0...n. I hope this helps you to help me and thank you for you time. Kind regards, D.Parker "Dave Peterson" wrote: If you want to look at the way the data is formatted in the cell, try changing: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Text Like "*.0" Then But I'm confused about the duplicates and the changes in formatting that you've described in the other posts. D.Parker wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
I'm baaaack.
Try this method. Assuming data cells are column A In B1 enter and drag/copy down. =A1-INT(A1)0.05 Will give TRUE or FALSE. 1.023 will be false. 1.2345 or 1.067 will be TRUE Filter out the TRUE's and format what's left to 1 DP If this gives you what you want, we can deal with weeding out the duplicates. Gord On Wed, 10 Oct 2007 09:37:04 -0700, D.Parker wrote: My apologies for any confusion. What happens is the following. 1) I open a .txt file in Excel. 2) The first column of data I format the numbers in the column to 1 decimal place ex. 1.23568 goes to 1.2 1.0235 goes to 1.0 and so on. 3) I have to go back and change the values to "Precision As Displayed" to TRY and retain the text format of 1.0, 1.2, 2.3, 4.0, 3.1, and so on. 4) When I do that the numbers that are 1.0, 2.0, 3.0 etc. are now 1, 2, 3 ...n. 5) When I run the algorithm the everything on my sheet is deleted. 6) I manually manipulated the cells to reflect 1.0, 2.0, 3.0...n and the algorithm work accordingly. Thus, my question was how to you keep the decimal and zero when you change the values to text keeping the Precision As Displayed? Otherwise, I have to embed a .0 after each whole number (i.e. 1, 2, 3, ...) in order to get the format 1.0, 2.0, 3.0. Or can the algorithm be changed to check for whole numbers in text format instead of "*.0"? Whichever method is easiest is preferred. After I manually manipulate the fields and the algorithm runs, the second problem is that I have multiple rows of 1.0, 2.0, 3.0 and I need to purge out the duplicate fields such that I go from a column A displaying 1.0, 1.0, 2.0, 2.0, 3.0, 3.0,...n,n to 1.0, 2.0, 3.0...n. I hope this helps you to help me and thank you for you time. Kind regards, D.Parker "Dave Peterson" wrote: If you want to look at the way the data is formatted in the cell, try changing: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Text Like "*.0" Then But I'm confused about the duplicates and the changes in formatting that you've described in the other posts. D.Parker wrote: 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 -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
Hello Dave:
To answer our first question if I manually change the cells from 0,1,2,3 to 0.0, 1.0, 2.0 and so on .value and .text works. But I have over 7K entries that i have to add the .0 to manually. The other option you suggested would be perfect, because I can work with whole numbers as I mentioned earlier. Unfortunately when I repalced the line If Not wks.Cells(i, 1).Value Like "*.0" Then with If Int(wks.Cells(i, 1).Value) = wks.Cells(i, 1).Value Then Nothing happened at all when I ran the code. The execution was almost instanteous but unfortunately the worksheet and data stayed the same. Any fixes to the second option would be great!! That would be the best path for this operation. Kind regards, D.Parker "Dave Peterson" wrote: So if you format the cells as 0.0 and use .text, does the code work ok? Or you could check to see if the value is a whole number: If int(wks.Cells(i, 1).value) = wks.cells(i,1).value then D.Parker wrote: My apologies for any confusion. What happens is the following. 1) I open a .txt file in Excel. 2) The first column of data I format the numbers in the column to 1 decimal place ex. 1.23568 goes to 1.2 1.0235 goes to 1.0 and so on. 3) I have to go back and change the values to "Precision As Displayed" to TRY and retain the text format of 1.0, 1.2, 2.3, 4.0, 3.1, and so on. 4) When I do that the numbers that are 1.0, 2.0, 3.0 etc. are now 1, 2, 3 ...n. 5) When I run the algorithm the everything on my sheet is deleted. 6) I manually manipulated the cells to reflect 1.0, 2.0, 3.0...n and the algorithm work accordingly. Thus, my question was how to you keep the decimal and zero when you change the values to text keeping the Precision As Displayed? Otherwise, I have to embed a .0 after each whole number (i.e. 1, 2, 3, ...) in order to get the format 1.0, 2.0, 3.0. Or can the algorithm be changed to check for whole numbers in text format instead of "*.0"? Whichever method is easiest is preferred. After I manually manipulate the fields and the algorithm runs, the second problem is that I have multiple rows of 1.0, 2.0, 3.0 and I need to purge out the duplicate fields such that I go from a column A displaying 1.0, 1.0, 2.0, 2.0, 3.0, 3.0,...n,n to 1.0, 2.0, 3.0...n. I hope this helps you to help me and thank you for you time. Kind regards, D.Parker "Dave Peterson" wrote: If you want to look at the way the data is formatted in the cell, try changing: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Text Like "*.0" Then But I'm confused about the duplicates and the changes in formatting that you've described in the other posts. D.Parker wrote: 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 -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
Gord:
Thank you for coming back, I would prefer to avoid inserting another column. Is there a way to check for text whole numbers in the code you supplied earlier? That would be best. Kind regards, D.Parker "Gord Dibben" wrote: I'm baaaack. Try this method. Assuming data cells are column A In B1 enter and drag/copy down. =A1-INT(A1)0.05 Will give TRUE or FALSE. 1.023 will be false. 1.2345 or 1.067 will be TRUE Filter out the TRUE's and format what's left to 1 DP If this gives you what you want, we can deal with weeding out the duplicates. Gord On Wed, 10 Oct 2007 09:37:04 -0700, D.Parker wrote: My apologies for any confusion. What happens is the following. 1) I open a .txt file in Excel. 2) The first column of data I format the numbers in the column to 1 decimal place ex. 1.23568 goes to 1.2 1.0235 goes to 1.0 and so on. 3) I have to go back and change the values to "Precision As Displayed" to TRY and retain the text format of 1.0, 1.2, 2.3, 4.0, 3.1, and so on. 4) When I do that the numbers that are 1.0, 2.0, 3.0 etc. are now 1, 2, 3 ...n. 5) When I run the algorithm the everything on my sheet is deleted. 6) I manually manipulated the cells to reflect 1.0, 2.0, 3.0...n and the algorithm work accordingly. Thus, my question was how to you keep the decimal and zero when you change the values to text keeping the Precision As Displayed? Otherwise, I have to embed a .0 after each whole number (i.e. 1, 2, 3, ...) in order to get the format 1.0, 2.0, 3.0. Or can the algorithm be changed to check for whole numbers in text format instead of "*.0"? Whichever method is easiest is preferred. After I manually manipulate the fields and the algorithm runs, the second problem is that I have multiple rows of 1.0, 2.0, 3.0 and I need to purge out the duplicate fields such that I go from a column A displaying 1.0, 1.0, 2.0, 2.0, 3.0, 3.0,...n,n to 1.0, 2.0, 3.0...n. I hope this helps you to help me and thank you for you time. Kind regards, D.Parker "Dave Peterson" wrote: If you want to look at the way the data is formatted in the cell, try changing: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Text Like "*.0" Then But I'm confused about the duplicates and the changes in formatting that you've described in the other posts. D.Parker wrote: 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 -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
Correction
=A1-INT(A1)=0.05 Gord On Wed, 10 Oct 2007 15:02:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: I'm baaaack. Try this method. Assuming data cells are column A In B1 enter and drag/copy down. =A1-INT(A1)0.05 Will give TRUE or FALSE. 1.023 will be false. 1.2345 or 1.067 will be TRUE Filter out the TRUE's and format what's left to 1 DP If this gives you what you want, we can deal with weeding out the duplicates. Gord |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
Don't change the value in each of the cells.
Select the range (whole column????) and format|Cells|number tab|Number, 1 decimal place. And use the .text version. D.Parker wrote: Hello Dave: To answer our first question if I manually change the cells from 0,1,2,3 to 0.0, 1.0, 2.0 and so on .value and .text works. But I have over 7K entries that i have to add the .0 to manually. The other option you suggested would be perfect, because I can work with whole numbers as I mentioned earlier. Unfortunately when I repalced the line If Not wks.Cells(i, 1).Value Like "*.0" Then with If Int(wks.Cells(i, 1).Value) = wks.Cells(i, 1).Value Then Nothing happened at all when I ran the code. The execution was almost instanteous but unfortunately the worksheet and data stayed the same. Any fixes to the second option would be great!! That would be the best path for this operation. Kind regards, D.Parker "Dave Peterson" wrote: So if you format the cells as 0.0 and use .text, does the code work ok? Or you could check to see if the value is a whole number: If int(wks.Cells(i, 1).value) = wks.cells(i,1).value then D.Parker wrote: My apologies for any confusion. What happens is the following. 1) I open a .txt file in Excel. 2) The first column of data I format the numbers in the column to 1 decimal place ex. 1.23568 goes to 1.2 1.0235 goes to 1.0 and so on. 3) I have to go back and change the values to "Precision As Displayed" to TRY and retain the text format of 1.0, 1.2, 2.3, 4.0, 3.1, and so on. 4) When I do that the numbers that are 1.0, 2.0, 3.0 etc. are now 1, 2, 3 ...n. 5) When I run the algorithm the everything on my sheet is deleted. 6) I manually manipulated the cells to reflect 1.0, 2.0, 3.0...n and the algorithm work accordingly. Thus, my question was how to you keep the decimal and zero when you change the values to text keeping the Precision As Displayed? Otherwise, I have to embed a .0 after each whole number (i.e. 1, 2, 3, ...) in order to get the format 1.0, 2.0, 3.0. Or can the algorithm be changed to check for whole numbers in text format instead of "*.0"? Whichever method is easiest is preferred. After I manually manipulate the fields and the algorithm runs, the second problem is that I have multiple rows of 1.0, 2.0, 3.0 and I need to purge out the duplicate fields such that I go from a column A displaying 1.0, 1.0, 2.0, 2.0, 3.0, 3.0,...n,n to 1.0, 2.0, 3.0...n. I hope this helps you to help me and thank you for you time. Kind regards, D.Parker "Dave Peterson" wrote: If you want to look at the way the data is formatted in the cell, try changing: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Text Like "*.0" Then But I'm confused about the duplicates and the changes in formatting that you've described in the other posts. D.Parker wrote: 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 -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
Worked for me changing .Value to .Text
Thanks Dave. Gord On Wed, 10 Oct 2007 21:37:17 -0500, Dave Peterson wrote: Don't change the value in each of the cells. Select the range (whole column????) and format|Cells|number tab|Number, 1 decimal place. And use the .text version. |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
David and Gord:
I manipulated the code to look for a interger value as opposed to the .0 values the following way--- from: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Value Like Int(wks.Cells(i, 1).Value) Then And it worked perfectly, now I just have duplicate rows of 0,0,1,1,2,2....n,n that I will be working on. Thank you for all of your time, talent and expertise. I really learned alot with these new functions. Kind regards, D.Parker "Gord Dibben" wrote: Correction =A1-INT(A1)=0.05 Gord On Wed, 10 Oct 2007 15:02:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: I'm baaaack. Try this method. Assuming data cells are column A In B1 enter and drag/copy down. =A1-INT(A1)0.05 Will give TRUE or FALSE. 1.023 will be false. 1.2345 or 1.067 will be TRUE Filter out the TRUE's and format what's left to 1 DP If this gives you what you want, we can deal with weeding out the duplicates. Gord |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
All that does for me is wipe everything from the sheet but if you're happy, go
for it. Gord On Wed, 10 Oct 2007 20:03:00 -0700, D.Parker wrote: David and Gord: I manipulated the code to look for a interger value as opposed to the .0 values the following way--- from: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Value Like Int(wks.Cells(i, 1).Value) Then And it worked perfectly, now I just have duplicate rows of 0,0,1,1,2,2....n,n that I will be working on. Thank you for all of your time, talent and expertise. I really learned alot with these new functions. Kind regards, D.Parker "Gord Dibben" wrote: Correction =A1-INT(A1)=0.05 Gord On Wed, 10 Oct 2007 15:02:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: I'm baaaack. Try this method. Assuming data cells are column A In B1 enter and drag/copy down. =A1-INT(A1)0.05 Will give TRUE or FALSE. 1.023 will be false. 1.2345 or 1.067 will be TRUE Filter out the TRUE's and format what's left to 1 DP If this gives you what you want, we can deal with weeding out the duplicates. Gord |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting Rows and Shifting Up - Repost
You are now having the same result I was having when we first started,
everything was being wiped from the sheet. I made the changes from values to text you suggested and I did not have that problem anymore (as well as I added the precision in the optionscalculation tab). You and Dave's expertise really, really, really got me over my hump. Thank you very much!!!! Kind regards, D.Parker "Gord Dibben" wrote: All that does for me is wipe everything from the sheet but if you're happy, go for it. Gord On Wed, 10 Oct 2007 20:03:00 -0700, D.Parker wrote: David and Gord: I manipulated the code to look for a interger value as opposed to the .0 values the following way--- from: If Not wks.Cells(i, 1).Value Like "*.0" Then to If Not wks.Cells(i, 1).Value Like Int(wks.Cells(i, 1).Value) Then And it worked perfectly, now I just have duplicate rows of 0,0,1,1,2,2....n,n that I will be working on. Thank you for all of your time, talent and expertise. I really learned alot with these new functions. Kind regards, D.Parker "Gord Dibben" wrote: Correction =A1-INT(A1)=0.05 Gord On Wed, 10 Oct 2007 15:02:33 -0700, Gord Dibben <gorddibbATshawDOTca wrote: I'm baaaack. Try this method. Assuming data cells are column A In B1 enter and drag/copy down. =A1-INT(A1)0.05 Will give TRUE or FALSE. 1.023 will be false. 1.2345 or 1.067 will be TRUE Filter out the TRUE's and format what's left to 1 DP If this gives you what you want, we can deal with weeding out the duplicates. Gord |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shifting rows into columns | Excel Discussion (Misc queries) | |||
Deleting and shifting cells and columns | Excel Worksheet Functions | |||
Deleting cells and shifting "right" | Excel Discussion (Misc queries) | |||
Shifting Date from all data on one row to several rows | Excel Discussion (Misc queries) | |||
Auto Update upon shifting, inserting or deleting | Excel Worksheet Functions |