[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 377
  • Last Modified:

Deleting Rows (from a certain row number until another row with a title)

Hello Experts,

Another spin to an earlier question.

Have a number of different worksheets that contain data. I would like to delete rows within each worksheet from Row 6 until the row that contains text such as "Row Header Data" less 1 row. For example, if in the worksheet named "AREA_1" the text was in row 200, I would delete rows from 6 to 199. I would also like to delete the first row in the individual worksheets.

This needs to be completed for a number of sheets, as named below in the code.

Regards

LK
Sheets("AREA_1").Cells.ClearContents
Sheets("AREA_2").Cells.ClearContents
Sheets("COUNTRY_1").Cells.ClearContents
Sheets("COUNTRY_2").Cells.ClearContents
Sheets("COUNTRY_3").Cells.ClearContents
Sheets("CITY_1").Cells.ClearContents
Sheets("CITY_2").Cells.ClearContents
Sheets("CITY_3").Cells.ClearContents

Open in new window

0
lkirke
Asked:
lkirke
1 Solution
 
Arno KosterCommented:
there are a number of strategies you can follow :

 - loop through rows 6 until end, look for row header data and delete row if not found & exit loop when text is found.
 - search for occurances of the row header data, use the cell adress to delete everything in between

The latter would be the better option.

Sub process()

    searchtext = "TEST"
    
    Set result = UsedRange.Find(what:=searchtext, LookIn:=xlValues)
    If Not result Is Nothing Then
        Range("6:" & result.Row - 1).Delete
    End If

End Sub

Open in new window

0
 
Arno KosterCommented:
or, to incorporate the sheets :


Sub process()
    Dim searchtext
    Dim sheets()
    
    searchtext = "TEST"
    sheets = Array("AREA_1","AREA_2","COUNTRY_1","COUNTRY_2","COUNTRY_3","CITY_1","CITY_2","CITY_3")
   
    For Each Item In sheets
        Set result = Worksheets(Item).UsedRange.Find(what:=searchtext, LookIn:=xlValues)
        If Not result Is Nothing Then
            Worksheets(Item).Range("6:" & result.Row - 1).ClearContents
        End If
    Next Item

End Sub

Open in new window

0
 
TinTombStoneCommented:
This should do it

Sub DeleteRows()
 
Dim foundRow As Long
Dim blFound As Boolean
Dim limitText As String
Dim sheetNames As Variant
Dim loopX As Long

    sheetNames = Array("Area_1", "Area_2", "Country_1", "Country_2", "Country_3", "CITY_1", "CITY_2", "CITY_3")
    limitText = "Row Header Data"

    For loopX = LBound(sheetNames) To UBound(sheetNames)
        Sheets(sheetNames(loopX)).Activate

        blFound = Cells.Find(What:=limitText, After:=Cells(1, 1), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
   
        If blFound Then
            foundRow = ActiveCell.Row - 1
            Rows("6:" & foundRow).EntireRow.Delete
        End If
   
    Rows(1).EntireRow.Delete
   
    Next loopX

End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
smartchapsCommented:
Dear TinTombStone

If the searched text is found then ok otherwise gives error 'Object variable or with block variable not set'.

akoster's code works fine.
0
 
Arno KosterCommented:
it works fine but i now see that you might want to include something like

Worksheets(Item).Range("1:1").ClearContents

Open in new window


to also clear the first row !
0
 
lkirkeAuthor Commented:
Hello Experts.

Attempted the solution by Akoster. Unfortunately doesn't work as expected. It clears data out in rows 5 to 6. As per the original requirement, the request:

Have a number of different worksheets that contain data. I would like to delete rows within each worksheet from Row 6 until the row that contains text such as "Row Header Data" less 1 row. For example, if in the worksheet named "AREA_1" the text was in row 200, I would delete rows from 6 to 199. I would also like to delete the first row in the individual worksheets.

This needs to be completed for a number of sheets, as named below in the code
.

Area-Sample.xls
0
 
Arno KosterCommented:
lkirke,

the only way that this would be possible is when you have placed the searchtext contents somewhere in row 6, looking at the code line

Worksheets(Item).Range("6:" & result.Row - 1)

The way the code is setup, it looks for searchtext in the complete usedrange, thus all the used columns.
Looking at your sample, cell A6 contains the searchtext.

when you want to search for cells containing only the searchtext, and nothing more, replace the for loop by

 
   For Each Item In sheets
        Set result = Worksheets(Item).UsedRange.Find(what:=searchtext, LookIn:=xlValues, lookat:=xlWhole)
        If Not result Is Nothing Then
            Worksheets(Item).Range("1:1").ClearContents
            Worksheets(Item).Range("7:" & result.Row - 1).ClearContents
        End If
    Next Item

Open in new window


note that the lookat xlWhole option has been added, and the row 6 remains as it is. last post indicates that row 6 should be deleted, but the sample indicates that it should stay.
0
 
lkirkeAuthor Commented:
Thank  you akoster. Attempted to incorporate the solution and received a Compile error: variable not defined.

Have attached an example. In this example, have refined the code to only look at two sheets within the workbook. The highlighted orange rows are the ones that need to be deleted.

Any ideas as to what is causing the error?
Area-Sample-v1.xls
0
 
Arno KosterCommented:
the compile error is introduced because of the use of "Option Explicit" on top of the VBA code.
This requires variables to be declared before use. Therefor the correct code would be :
Public Sub Delete_Rows()
    Dim searchtext
    Dim sheets()
    Dim item
    Dim result
    
    searchtext = "DELETE FROM THIS ROW DOWN"
    sheets = Array("Country_1", "Country_2")
   
   For Each item In sheets
        Set result = Worksheets(item).UsedRange.Find(what:=searchtext, LookIn:=xlValues)
        If Not result Is Nothing Then
            Worksheets(item).Range("1:1").Rows.Delete Shift:=xlUp
            Worksheets(item).Range("7:" & result.Row - 1).Rows.Delete Shift:=xlUp
        End If
    Next item

End Sub

Open in new window


please note though that this code will not delete any rows because the searchtext is not present in the current country sheets !
0
 
lkirkeAuthor Commented:
Great akoster. Works as expected.

To understand and apply better in the future, hoping I can ask some questions:

Why does Option Explicit require variables to be declared?
What is the difference between public sub and sub?

Regards and appreciation

LK
0
 
Arno KosterCommented:
no problems,

the use of option explicit is to require variables to be declared before use. This is (or should be)  common practice for most programming languages, because it increases the overall quality of the code. If you force yourself to declare variables in a specific object type, you are prone to save on memory usage and avoid confusion (incorrectly typing of variable names and use of variables out of scope will lead to design-time errors and thus can be fixed before difficult to debug problems arise)

a 'sub' is exactly the same as a 'public sub'. it means that the subroutine can also be used outside of the scope of the module or VBA page where it is placed.
a private sub can only be used from within the module or VBA page it resides in.
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now