If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
removing specific rows in a worksheet
Hi all
I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#2
|
|||
|
|||
removing specific rows in a worksheet
Hi Louise,
Try this backup your file first I suppose that Store Manager is in column C, change it to fit your requirements Sub delete_Me() Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row Set MyRange = Range("C1:C" & Lastrow) For Each c In MyRange If InStr(c, "Store Manager") Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#3
|
|||
|
|||
removing specific rows in a worksheet
Louise;193598 Wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise Hi, have a look at 'this site' (http://www.rondebruin.nl/delete.htm) under the title "Code example" and adapt to your needs. If you don't succeed, joining as member ( free) allows to ulpoad a sample file. I can then do it for you. -- Pecoflyer Cheers - *'Membership is free' (http://www.thecodecage.com)* & allows file upload -faster and better answers *Adding your XL version* to your post helps finding solution faster ------------------------------------------------------------------------ Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=53391 |
#4
|
|||
|
|||
removing specific rows in a worksheet
Filter on 'Starts with' Store Manager
Delete the filtered rows (You may have to press F5 and Choose Special and then click on Visible Cells only) Do make a copy before experimenting or make sure that you do not save till you are satisfied with the results. "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#5
|
|||
|
|||
removing specific rows in a worksheet
Hi Eduardo
Thank you for your prompt reply. I can't seem to get this to work. I have changed the column letter to A and have run the macro but nothing happens? I have shown below how i have edited the macro - have I done something wrong or is there something on here still referencing column C, as in your example, that I've missed? Sub test() Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & Lastrow) For Each c In MyRange If InStr(c, "Store Manager") Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Thanks again. Louise "Eduardo" wrote: Hi Louise, Try this backup your file first I suppose that Store Manager is in column C, change it to fit your requirements Sub delete_Me() Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row Set MyRange = Range("C1:C" & Lastrow) For Each c In MyRange If InStr(c, "Store Manager") Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#6
|
|||
|
|||
removing specific rows in a worksheet
Hello
This does remove the text, however the row itself remains, I need to remove the actual row too. Any ideas? Thanks. Louise "Sheeloo" wrote: Filter on 'Starts with' Store Manager Delete the filtered rows (You may have to press F5 and Choose Special and then click on Visible Cells only) Do make a copy before experimenting or make sure that you do not save till you are satisfied with the results. "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#7
|
|||
|
|||
removing specific rows in a worksheet
I've tried this again and it seems to have worked. Thanks for your help.
Louise "Sheeloo" wrote: Filter on 'Starts with' Store Manager Delete the filtered rows (You may have to press F5 and Choose Special and then click on Visible Cells only) Do make a copy before experimenting or make sure that you do not save till you are satisfied with the results. "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#8
|
|||
|
|||
removing specific rows in a worksheet
Hi Louise,
I run it and is working, please check that the name you have in the macro is the same in the spreadsheet, look at the blanks "Louise" wrote: Hi Eduardo Thank you for your prompt reply. I can't seem to get this to work. I have changed the column letter to A and have run the macro but nothing happens? I have shown below how i have edited the macro - have I done something wrong or is there something on here still referencing column C, as in your example, that I've missed? Sub test() Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row Set MyRange = Range("A1:A" & Lastrow) For Each c In MyRange If InStr(c, "Store Manager") Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub Thanks again. Louise "Eduardo" wrote: Hi Louise, Try this backup your file first I suppose that Store Manager is in column C, change it to fit your requirements Sub delete_Me() Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "C").End(xlUp).Row Set MyRange = Range("C1:C" & Lastrow) For Each c In MyRange If InStr(c, "Store Manager") Then If copyrange Is Nothing Then Set copyrange = c.EntireRow Else Set copyrange = Union(copyrange, c.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Delete End If End Sub "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#9
|
|||
|
|||
removing specific rows in a worksheet
Hi Louise,
Check the blanks between Store and Manager, my code is working for me "Louise" wrote: Hello This does remove the text, however the row itself remains, I need to remove the actual row too. Any ideas? Thanks. Louise "Sheeloo" wrote: Filter on 'Starts with' Store Manager Delete the filtered rows (You may have to press F5 and Choose Special and then click on Visible Cells only) Do make a copy before experimenting or make sure that you do not save till you are satisfied with the results. "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
#10
|
|||
|
|||
removing specific rows in a worksheet
Select the entire column and sort. Find where all of the "store manager"s are. Select your rows, right click (or Ctrl+click if on a mac), select Delete. "Louise" wrote: Hi all I have a large worksheet containing thousands of rows beginning with the words 'store manager'. I need to remove every row in the workbook beginning with these two words. Is there an easy way to do this? Thank you. Louise |
Thread Tools | |
Display Modes | |
|
|