VBA: Erase Row containing PAGE (works); need help to remove 8 rows below as well

[envisioning]

Hi guys, I've been working on a VBA code and I need to edit it in a way to delete the row with PAGE in it, as well as the next 8 rows following it.

I have a text file that is being imported and there are over 500 pages, with each page having the same heading, date, etc. etc.. I need to remove the unneccesary rows.

Heres the code so far, and it works like a charm.

==============================
Sub Delete_Rows_with_PAGE()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long
Dim ViewMode As Long
With Application
CalcMode = .Calculation
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With
With ActiveSheet
.Select
ViewMode = ActiveWindow.View
ActiveWindow.View = xlNormalView
.DisplayPageBreaks = False
Firstrow = .UsedRange.Cells(1).Row
Lastrow = .UsedRange.Rows(.UsedRange.Rows.Count).Row
For Lrow = Lastrow To Firstrow Step -1

If Application.CountIf(.Rows(Lrow), "*PAGE*") > 0 _
Then .Rows(Lrow).Delete

Next Lrow
End With
ActiveWindow.View = ViewMode
With Application
.ScreenUpdating = True
.Calculation = CalcMode
End With
End Sub
=================================

I tried fiddling with this for a while, but can not seem to find the right coding to have it erase this row with "PAGE" in it, as well as the next 8 lines.

So if row "A" had "PAGE" in it, I would want this code to remove rows A to I completely.

Thanks in advance!

 

[stnkynts]

Have you tried using the Resize Property

 

[envisioning]

I'm not exactly great at VBA and have been unable to correctly code the Resize Property into the equation.

I keep getting error codes.

 

[Leith Ross]

Hello envisioning,

Try this modification of your macro.

Sub Delete_Rows_With_PAGE()

  Dim CalcMode
  Dim LastRow As Range
  Dim Row As Range
  Dim Rng As Range
  Dim Text As String
  Dim Wks As Worksheet
  
    Text = "page"
    
    Set Wks = ThisWorkbook.Worksheets("Sheet1")
    Set Rng = Wks.Range("A1").EntireRow
    
    Set LastRow = Wks.UsedRange.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False, False)
    If LastRow Is Nothing Then Exit Sub
    Set Rng = Rng.Resize(RowSize:=LastRow.Row + Rng.Row - 1)
    
      CalcMode = Application.Calculation
      Application.Calculation = xlCalculationManual
      Application.ScreenUpdating = False
      
        For Each Row In Rng.Rows
          If WorksheetFunction.CountIf(Row, Text) Then
             Row.Resize(RowSize:=9).EntireRow.Delete
          End If
        Next Row
      
      Application.Calculation = CalcMode
      Application.ScreenUpdating = True
      
End Sub

Sincerely,
Leith Ross

 

[envisioning]

Leith Ross,

Thanks alot! It works great! I had to make it *PAGE* with the asterics and it worked exactly as I wanted it to.

Again, thank you for your time!

 

[envisioning]

WORKS! Now need a little twist... A conditional statement?

The code I am using now looks like this:
====================================

[SIZE=1]Sub Delete_Rows_With_Report_Generated_Using_List_Name_FINAL_1()[/SIZE]
[SIZE=1]Dim CalcMode[/SIZE]
[SIZE=1]Dim LastRow As Range[/SIZE]
[SIZE=1]Dim Row As Range[/SIZE]
[SIZE=1]Dim Rng As Range[/SIZE]
[SIZE=1]Dim Text As String[/SIZE]
[SIZE=1]Dim Wks As Worksheet[/SIZE]
 
[SIZE=1]  Text = "*PAGE*"[/SIZE]
 
[SIZE=1]  Set Wks = ThisWorkbook.Worksheets("Sheet1")[/SIZE]
[SIZE=1]  Set Rng = Wks.Range("A1").EntireRow[/SIZE]
 
[SIZE=1]  Set LastRow = Wks.UsedRange.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False, False)[/SIZE]
[SIZE=1]  If LastRow Is Nothing Then Exit Sub[/SIZE]
[SIZE=1]  Set Rng = Rng.Resize(RowSize:=LastRow.Row + Rng.Row - 1)[/SIZE]
 
[SIZE=1]    CalcMode = Application.Calculation[/SIZE]
[SIZE=1]    Application.Calculation = xlCalculationManual[/SIZE]
[SIZE=1]    Application.ScreenUpdating = False[/SIZE]
 
[SIZE=1]      For Each Row In Rng.Rows[/SIZE]
[SIZE=1]        If WorksheetFunction.CountIf(Row, Text) Then[/SIZE]
[SIZE=1]           Row.Resize(RowSize:=10).EntireRow.Delete[/SIZE]
[SIZE=1]        End If[/SIZE]
[SIZE=1]      Next Row[/SIZE]
 
[SIZE=1]    Application.Calculation = CalcMode[/SIZE]
[SIZE=1]    Application.ScreenUpdating = True[/SIZE]
 
[SIZE=1]End Sub[/SIZE]

Is there any way for there to be a conditional IF in there too? I need it to check to see if there is a NONBLANK cell in column A, one row above the range and one row below the range.

Basically, every time there is data in the A column, it means it is a new CABLE. For the rest of my macros to work, I need each new CABLE to be separated by a blank row.

Every so many lines (in the text report), it inserts a header with a few rows of junk wording (hence the reason behind this macro to erase the 10 rows).

So, is there a way to make this macro FIND a row with "Page" in it, then check to see if column A (one row above) is NONBLANK?

If it is NONBLANK, then I would need it to check 10 rows down to see if Column A is NONBLANK.

If both conditions are TRUE, then ERASE the 10 rows, and insert a blank row.

Otherwise, just erase the 10 rows.

Again the logic:
1) find "PAGE" in a row
2) look one row up in column A to see if it is NONBLANK
3) If false, erase 10 rows
4) If TRUE, then check 10 rows down in column A to see if NONBLANK
5) If TRUE, then erase 10 rows, and insert one blank row
6) If FALSE, then erase 10 rows

Is this possible?

 

[edokhotnik]

Hello envisioning,

Try this modification of your macro.

Sub Delete_Rows_With_PAGE()

  Dim CalcMode
  Dim LastRow As Range
  Dim Row As Range
  Dim Rng As Range
  Dim Text As String
  Dim Wks As Worksheet
  
    Text = "page"
    
    Set Wks = ThisWorkbook.Worksheets("Sheet1")
    Set Rng = Wks.Range("A1").EntireRow
    
    Set LastRow = Wks.UsedRange.Find("*", , xlValues, xlPart, xlByRows, xlPrevious, False, False)
    If LastRow Is Nothing Then Exit Sub
    Set Rng = Rng.Resize(RowSize:=LastRow.Row + Rng.Row - 1)
    
      CalcMode = Application.Calculation
      Application.Calculation = xlCalculationManual
      Application.ScreenUpdating = False
      
        For Each Row In Rng.Rows
          If WorksheetFunction.CountIf(Row, Text) Then
             Row.Resize(RowSize:=9).EntireRow.Delete
          End If
        Next Row
      
      Application.Calculation = CalcMode
      Application.ScreenUpdating = True
      
End Sub

Sincerely,
Leith Ross

I am trying to do something similar, but with columns. How would I change this to delete columns that contain a particular text string? I would only need to delete the column that contains the text. Thanks for your help.

Ed

 

[envisioning]

I tried messing with it but to no avail. I am still learning the ins and outs of the VBA coding and the Excel language.