Help. How to find a cell text in a spreadsheet and delete row below

[Excel Newbie7]

For example I have a spreadsheet that has a certain word that only shows up everyone once in a while and mess up my macro.

The text is in column F and is [TABLE="width: 241"]
<TBODY>[TR]
[TD]"Calculated Beginning History Balance"</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

I need to delete the row below that so that the calculations would be correct.

I have no clue how to do this. I tride a auto find and do an offset(1,0) then delete row, but it doesn't work.

 

[njimack]

Welcome to the board. The below line of code should work for you:
Cells.Find("Calculated Beginning History Balance").Offset(1).EntireRow.Delete

 

[Rick Rothstein]

Welcome to the board. The below line of code should work for you:
Cells.Find("Calculated Beginning History Balance").Offset(1).EntireRow.Delete

Since the OP said the text was in Column F, I would change the highlighted Cells to Columns("F") instead.

 

[L. Howard]

Try this in the sheet module.
Enter this in A1, with NO " "'s (or cell of choice and change code to that cell) Calculated Beginning History Balance.

Or you can hard code that string into the code with:
(And note the " "'s)

FindCBHB = "Calculated Beginning History Balance"

This code Deletes the entire row, if you only want to clear that row below the search string then use this:

Rng.Offset(1, 0).EntireRow.ClearContents

Regards,
Howard

Option Explicit

Sub FColmXit()
Dim FindCBHB As String
Dim ws As Worksheet
Dim Rng As Range
Dim LRow As Long

FindCBHB = Sheets("Sheet1").Range("A1")
    
    With ActiveSheet
         LRow = .Cells(.Rows.Count, 6).End(xlUp).Row
         Set Rng = .Range("F1:F" & LRow).Find(FindCBHB, _
            LookIn:=xlValues)
         If Not Rng Is Nothing Then
            Rng.Offset(1, 0).EntireRow.Delete
         Else
            MsgBox "No value for FindCBHB"
         End If
      End With

Range("A1").Select
End Sub

 

[Excel Newbie7]

Thanks a ton this helped.

 

[Excel Newbie7]

Thanks this was what I was looking for with the coloumn F