Hi,
I need to delete rows between the words "Description" and "Transportation" every time they appear in column A, including deleting the rows those words appear in.
For example:
row 1-Description
row 2
row 3-Transportation
row 4
row 5-Description
row 6
row 7
row 8
row 9- Transportation
etc...
In this example, the result needs to be that only row 4 (and the ones after row 9) are still in the sheet.
I found a code but it has 2 problems:
1. It only deletes the first Description-Transportation couple (meaning rows 5-9 are still there).
2. The rows with those words stay on the sheet.
Thanks a lot!
PS I don't have to use this specific code, it just looked like it could help.
Sub Macro3()
Sub HideDeleteDT()
Const cSheet As Variant = "Sheet1"
Const cStr1 As String = "Description"
Const cStr2 As String = "Transportation"
Const cCol As Variant = "A"
Dim Find1 As Range
Dim Find2 As Range
Dim LCell As Range
With ThisWorkbook.Worksheets(cSheet)
With .Columns(cCol)
Set LCell = .Cells(.Cells.Count)
Set Find1 = .Find(cStr1, LCell, xlValues, xlWhole, xlByColumns)
End With
If Not Find1 Is Nothing Then
Set Find2 = .Range(Find1.Offset(1), LCell).Find(cStr2, LCell)
If Not Find2 Is Nothing Then
If Find1.Row + 1 < Find2.Row Then
If cDel Then
.Rows(Find1.Row + 1 & ":" & Find2.Row - 1).Delete
Else
.Rows.Hidden = False
.Rows(Find1.Row + 1 & ":" & Find2.Row - 1).Hidden = True
End If
End If
End If
End If
End With
End Sub
I need to delete rows between the words "Description" and "Transportation" every time they appear in column A, including deleting the rows those words appear in.
For example:
row 1-Description
row 2
row 3-Transportation
row 4
row 5-Description
row 6
row 7
row 8
row 9- Transportation
etc...
In this example, the result needs to be that only row 4 (and the ones after row 9) are still in the sheet.
I found a code but it has 2 problems:
1. It only deletes the first Description-Transportation couple (meaning rows 5-9 are still there).
2. The rows with those words stay on the sheet.
Thanks a lot!
PS I don't have to use this specific code, it just looked like it could help.
Sub Macro3()
Sub HideDeleteDT()
Const cSheet As Variant = "Sheet1"
Const cStr1 As String = "Description"
Const cStr2 As String = "Transportation"
Const cCol As Variant = "A"
Dim Find1 As Range
Dim Find2 As Range
Dim LCell As Range
With ThisWorkbook.Worksheets(cSheet)
With .Columns(cCol)
Set LCell = .Cells(.Cells.Count)
Set Find1 = .Find(cStr1, LCell, xlValues, xlWhole, xlByColumns)
End With
If Not Find1 Is Nothing Then
Set Find2 = .Range(Find1.Offset(1), LCell).Find(cStr2, LCell)
If Not Find2 Is Nothing Then
If Find1.Row + 1 < Find2.Row Then
If cDel Then
.Rows(Find1.Row + 1 & ":" & Find2.Row - 1).Delete
Else
.Rows.Hidden = False
.Rows(Find1.Row + 1 & ":" & Find2.Row - 1).Hidden = True
End If
End If
End If
End If
End With
End Sub