Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.0k views
in Technique[技术] by (71.8m points)

vba - Skip one worksheet and process the remaining worksheets

I was working with a vba code which remove all the rows which dont have word "Statement No" in it from all available worksheets. But anyhow i want it to skip the first worksheet and continue removing the rows from all other worksheets.

If you can help me with the below code so that it skips the first worksheet.

Sub doit()

Application.DisplayAlerts = False

Dim r As Long, lr As Long
Dim sh As Worksheet



For Each sh In Sheets

lr = sh.Cells(sh.Rows.Count, 1).End(xlUp).row
For r = lr To 1 Step -1
    If InStr(sh.Cells(r, 1), "Statement No") = 0 Then sh.Rows(r).Delete
Next r
Next

Application.DisplayAlerts = True

End Sub

Thank you All!

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

Just tell it to ignore the sheet by checking each sheet name:

Sub doit()

    Application.DisplayAlerts = False

    Dim r As Long, lr As Long
    Dim sh As Worksheet

    For Each sh In Sheets
        If sh.Name <> "IgnoreThisSheet" Then
            lr = sh.Cells(sh.Rows.Count, 1).End(xlUp).Row
            For r = lr To 1 Step -1
                If InStr(sh.Cells(r, 1), "Statement No") = 0 Then sh.Rows(r).Delete
            Next r
        End If
    Next

    Application.DisplayAlerts = True

End Sub

Edit: You may also want to change Sheets to WorkSheets in your For Each loop. A worksheet contains rows, columns, etc. A sheet can be a chart sheet, macro sheet or dialog sheet. http://blogs.msdn.com/b/frice/archive/2007/12/05/excel-s-worksheets-and-sheets-collection-what-s-the-difference.aspx


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...