Delete Nth Row In All Worksheets If Cell Meets Criteria

jonny · May 21st 2008

I want to scan all sheets in a workbook and to delete a first row where a value in cell(1,1) is "table".

Code

Sub DeleteFirstRowInWorksheet()
    Dim SheetName As Worksheet
    Dim i As Integer


    For Each SheetName In Sheets
        If Range("A1") = "table" Then
            Rows("1:1").Select
            Selection.Delete Shift:=xlUp
        End If
    Next SheetName
End Sub

Display More

It delete only in an active sheet. What's wrong?

**Ger Plante** · May 21st 2008

Re: Delete First Row In Worksheets

Try -

Code

Sub DeleteFirstRowInWorksheet() 
    Dim SheetName As Worksheet 
    Dim i As Integer 
     
    For Each SheetName In Sheets 
        If SheetName.Range("A1") = "table" Then 
            SheetName.Rows("1:1").Delete Shift:=xlUp 
        End If 
    Next SheetName 
End Sub

Ger

jonny · May 21st 2008

Re: Delete First Row In Worksheets

Still staying on a same worksheet.., may be problem that I have ~70 worksheets?

Dave Hawley · May 21st 2008

Re: Delete Nth Row In All Worksheets If Cell Meets Criteria

Why do you want the code to switch sheets?

**Ger Plante** · May 21st 2008

Re: Delete First Row In Worksheets

Hi Jonny, number of worksheets is irrelevant.

The string "table" must match exactly - it is case sensitive.

To avoid, this convert the string to uppercase and then compare...

Code

Option Explicit


Sub DeleteFirstRowInWorksheet()
    Dim SheetName As Worksheet
    Dim i As Integer
     
    For Each SheetName In Sheets
        If UCase(SheetName.Range("A1")) = "TABLE" Then
            SheetName.Rows("1:1").Delete Shift:=xlUp
        End If
    Next SheetName
End Sub

Display More

Also, the code does not actually activate each sheet - there is no need to activate or select a sheet, as long as you reference the sheet name.

Ger

jonny · May 21st 2008

Re: Delete Nth Row In All Worksheets If Cell Meets Criteria

Thank you , friends. Now it works!

Delete Nth Row In All Worksheets If Cell Meets Criteria

Participate now!

Share