Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Want to delete rows
I have text file which I import into Excel. There is a particular string
which I need to find (this can be in any row) so the row position is variable. I can find this string no problem. What I want to do is once I find the string, then starting from the row where the string is located, I want to delete all rows above including the row in which the desired string is located. e.g the string is at Row 27, delete rows 1-27. Thanks |
#2
|
|||
|
|||
So you first find the string. Once the string is found, you press CTRL-SHIFT
and the UP ARROW keys. This will select all the cells in the column up to the cell where the search string is in. Once you've done that, you choose EditDeleteEntire row. Or if what you mean is you have to repeat this process with many files, then you use following VBA code: Sub deleteRows() Dim dString As String Dim rng As Range dString = InputBox("Please enter the search string.") Set rng = ActiveSheet.UsedRange.Find(dString) If Not rng Is Nothing Then Range(rng, rng.End(xlUp)).EntireRow.Delete Else MsgBox ("Make sure you enter the correct string!") End If End Sub "Farooq Sheri" wrote in message ... I have text file which I import into Excel. There is a particular string which I need to find (this can be in any row) so the row position is variable. I can find this string no problem. What I want to do is once I find the string, then starting from the row where the string is located, I want to delete all rows above including the row in which the desired string is located. e.g the string is at Row 27, delete rows 1-27. Thanks |
#3
|
|||
|
|||
Manually???
Just find that troublesome word then hit ctrl-shift-home then Edit|Delete...|Entire row Farooq Sheri wrote: I have text file which I import into Excel. There is a particular string which I need to find (this can be in any row) so the row position is variable. I can find this string no problem. What I want to do is once I find the string, then starting from the row where the string is located, I want to delete all rows above including the row in which the desired string is located. e.g the string is at Row 27, delete rows 1-27. Thanks -- Dave Peterson |
#4
|
|||
|
|||
Thanks for the response. Doing it manually is trivial. What I wanted was a
VBA code which you have provided. I have used it in my Excel sheet but it only deletes the enitre row which contains the string. I also want to delete all rows above it. e.g if row 7 contains the string I want to delete rows 1-7 "Shatin" wrote: So you first find the string. Once the string is found, you press CTRL-SHIFT and the UP ARROW keys. This will select all the cells in the column up to the cell where the search string is in. Once you've done that, you choose EditDeleteEntire row. Or if what you mean is you have to repeat this process with many files, then you use following VBA code: Sub deleteRows() Dim dString As String Dim rng As Range dString = InputBox("Please enter the search string.") Set rng = ActiveSheet.UsedRange.Find(dString) If Not rng Is Nothing Then Range(rng, rng.End(xlUp)).EntireRow.Delete Else MsgBox ("Make sure you enter the correct string!") End If End Sub "Farooq Sheri" wrote in message ... I have text file which I import into Excel. There is a particular string which I need to find (this can be in any row) so the row position is variable. I can find this string no problem. What I want to do is once I find the string, then starting from the row where the string is located, I want to delete all rows above including the row in which the desired string is located. e.g the string is at Row 27, delete rows 1-27. Thanks |
#5
|
|||
|
|||
Replace the line:
Range(rng, rng.End(xlUp)).EntireRow.Delete with Rows("1:" & rng.Row).Delete Hope this helps Rowan Farooq Sheri wrote: Thanks for the response. Doing it manually is trivial. What I wanted was a VBA code which you have provided. I have used it in my Excel sheet but it only deletes the enitre row which contains the string. I also want to delete all rows above it. e.g if row 7 contains the string I want to delete rows 1-7 "Shatin" wrote: So you first find the string. Once the string is found, you press CTRL-SHIFT and the UP ARROW keys. This will select all the cells in the column up to the cell where the search string is in. Once you've done that, you choose EditDeleteEntire row. Or if what you mean is you have to repeat this process with many files, then you use following VBA code: Sub deleteRows() Dim dString As String Dim rng As Range dString = InputBox("Please enter the search string.") Set rng = ActiveSheet.UsedRange.Find(dString) If Not rng Is Nothing Then Range(rng, rng.End(xlUp)).EntireRow.Delete Else MsgBox ("Make sure you enter the correct string!") End If End Sub "Farooq Sheri" wrote in message ... I have text file which I import into Excel. There is a particular string which I need to find (this can be in any row) so the row position is variable. I can find this string no problem. What I want to do is once I find the string, then starting from the row where the string is located, I want to delete all rows above including the row in which the desired string is located. e.g the string is at Row 27, delete rows 1-27. Thanks |
#6
|
|||
|
|||
One thing that I just noted is that your code stops at the first empty row it
encounters deleting upwards. For example the string is at row 7. Row 4 is an empty row (no data). Your code will delete rows 5-7 but will stop at row 4. I think it has some thing to do with the way you have constructed your If statement. Thanks Farooq "Shatin" wrote: So you first find the string. Once the string is found, you press CTRL-SHIFT and the UP ARROW keys. This will select all the cells in the column up to the cell where the search string is in. Once you've done that, you choose EditDeleteEntire row. Or if what you mean is you have to repeat this process with many files, then you use following VBA code: Sub deleteRows() Dim dString As String Dim rng As Range dString = InputBox("Please enter the search string.") Set rng = ActiveSheet.UsedRange.Find(dString) If Not rng Is Nothing Then Range(rng, rng.End(xlUp)).EntireRow.Delete Else MsgBox ("Make sure you enter the correct string!") End If End Sub "Farooq Sheri" wrote in message ... I have text file which I import into Excel. There is a particular string which I need to find (this can be in any row) so the row position is variable. I can find this string no problem. What I want to do is once I find the string, then starting from the row where the string is located, I want to delete all rows above including the row in which the desired string is located. e.g the string is at Row 27, delete rows 1-27. Thanks |
#7
|
|||
|
|||
It sure does help. Thanks.
Farooq "Rowan" wrote: Replace the line: Range(rng, rng.End(xlUp)).EntireRow.Delete with Rows("1:" & rng.Row).Delete Hope this helps Rowan Farooq Sheri wrote: Thanks for the response. Doing it manually is trivial. What I wanted was a VBA code which you have provided. I have used it in my Excel sheet but it only deletes the enitre row which contains the string. I also want to delete all rows above it. e.g if row 7 contains the string I want to delete rows 1-7 "Shatin" wrote: So you first find the string. Once the string is found, you press CTRL-SHIFT and the UP ARROW keys. This will select all the cells in the column up to the cell where the search string is in. Once you've done that, you choose EditDeleteEntire row. Or if what you mean is you have to repeat this process with many files, then you use following VBA code: Sub deleteRows() Dim dString As String Dim rng As Range dString = InputBox("Please enter the search string.") Set rng = ActiveSheet.UsedRange.Find(dString) If Not rng Is Nothing Then Range(rng, rng.End(xlUp)).EntireRow.Delete Else MsgBox ("Make sure you enter the correct string!") End If End Sub "Farooq Sheri" wrote in message ... I have text file which I import into Excel. There is a particular string which I need to find (this can be in any row) so the row position is variable. I can find this string no problem. What I want to do is once I find the string, then starting from the row where the string is located, I want to delete all rows above including the row in which the desired string is located. e.g the string is at Row 27, delete rows 1-27. Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can we delete rows permanently from excel sheet | Excel Discussion (Misc queries) | |||
In a protected worksheet allow users to delete rows | Excel Worksheet Functions | |||
How do I find duplicate rows in a list in Excel, and not delete it | Excel Discussion (Misc queries) | |||
Protect Worksheet but allow to insert or delete rows | Excel Discussion (Misc queries) | |||
How to delete blank rows | Excel Discussion (Misc queries) |