August 30, 2008 02:17am pdt

1. zorvek 2,107,186
2. rorya 2,046,988
3. saurabh726 1,301,130
4. chris_bot... 776,246
5. byundt 680,182
6. nutsch 546,068
7. patrickab 412,280
8. jeverist 407,238
9. spattewar 316,278
10. matthews... 303,769
11. ahammar 281,891
12. webtubbs 256,684
13. jpaulino 245,997
14. RobSamp... 240,914
15. dj88 210,941
16. irudyk 163,728
17. WJReid 150,302
18. weellio 143,519
19. Psychotec 133,785
20. epaclm 119,855
21. GrahamSkan 107,769
22. jell 100,971
23. Merch_Ops 92,593
24. gtgloner 83,427
25. StellanRo... 80,929

1. zorvek 9,411,677
2. byundt 7,423,271
3. rorya 5,741,719
4. matthews... 2,866,897
5. jeverist 2,817,507
6. patrickab 2,167,843
7. roos01 1,919,714
8. mvidas 1,529,753
9. saurabh726 1,394,709
10. webtubbs 1,353,076
11. fanpages 1,234,722
12. brettdj 1,095,751
13. MalicUK 1,059,396
14. chris_bot... 965,752
15. mgh_mgh... 649,110
16. LoNeRaV... 602,049
17. ahammar 582,024
18. ture 570,551
19. nutsch 546,068
20. spattewar 525,227
21. R_Rajesh 468,975
22. jpaulino 358,896
23. david251 355,614
24. RobSamp... 339,463
25. sebastienm 339,407

06.26.2008 at 03:47PM PDT, ID: 23520109

	[x] Attachment Details

VBA loop to find cell w/ match, get that row and next 8 rows, and delete block

Asked by b0lsc0tt in Microsoft Excel Spreadsheet Software, VB Script

Tags: Excel 2007, VBA, VBScript

This isn't my expertise. I do know VB to a certain extent but not in relation to MS Office Apps. Trying to use VB in Excel to get rid of some blocks of rows. My sheet has 13K+ of data but every 40 or so there is a block of 9 rows I want to remove. The first row always starts with the text I use in the Find line (see snippet). I want to find the match and then call another Sub that will use that row, get the next 8 and delete the block. This should then loop through the sheet to get rid of all matches.

This seems to be working but just once. After the first row is deleted I get an error in the VB Editor. The error is:

Run-time error 1004
Application-defined or object-defined error

The Help explanation was not helpful enough. I am not use to using the VB Editor either so maybe I am overlooking some great tool or help there.

BTW, I just added the Option Explicit and am trying to make sure I properly declare variables. It wasn't there when I got the error so feel free to let me know if I need to add another declaration or two.

Feel free to correct me if I use an incorrect term. I can provide more details if needed. I know Excel pretty well but just haven't ever made a VBA. I have tried to avoid them like the plague but am actually excited about this project. There will actually be more steps to it too (which I may need help with). I am not new to programming or EE but appreciate your patience and help with this, especially if it comes with instruction and helps me learn this. I am not looking for someone to just do the work. ;)

Thanks!

bolStart Free Trial

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:

           
           
             Option Explicit
Public Sub DeleteExtra()
    Dim c As Range
    With Worksheets("Sheet2").Cells
        Set c = .Find(What:="CSALLRMSCHD*", LookAt:=xlPart, After:=ActiveCell, LookIn:=xlFormulas, SearchDirection:=xlNext)
        If Not c Is Nothing Then
            Do
                i = c.Row
                Call DeleteBlock(i)
                Set c = .FindNext(c)
            Loop While Not c Is Nothing
        End If
    End With
End Sub
 
 
Private Sub DeleteBlock(i)
    Dim myUnion As Range
    Set myUnion = Union(Rows(i), Rows(i + 1), Rows(i + 2), Rows(i + 3), Rows(i + 4), Rows(i + 5), Rows(i + 6), Rows(i + 7), Rows(i + 8))
    myUnion.EntireRow.Delete
End Sub

           
         

Keywords: VBA loop to find cell w/ match, get t…

	Title
1	Find Cell Value VBA
2	Cell Format via VBA
3	Count cells if ... VBA
4	clear cell values - vba
5	VBA AutoFill with Ranges and only 1 r…
6	VBA for Conditional Formatting in Excel

Loading Advertisement...

20080716-EE-VQP-32 / EE_QW_EXPERT_20070906

VBA loop to find cell w/ match, get that row and next 8 rows, and delete block

Asked by b0lsc0tt in Microsoft Excel Spreadsheet Software, VB Script

Tags: Excel 2007, VBA, VBScript

About this solution