FIND Select and Delete in Dynamic Range

[Edgarvelez]

Hi All,

I have 2 sheets

    Set sh1 = Sheets("Intake Macro")
    Set sh2 = Sheets("UpLoad Sheet")

sh1 is where I have my buttons for the macros and other stuff
sh2 is where my data and final results go.

In sh2 I am trying to find and select the cell where the word "TOTAL" is and select 6 rows across and delete.
However if it does not find the word TOTAL then I want it to do nothing and continue.
The range of rows is dynamic.
I tried recording CTR-F and grabbing the code that way, but I am not at that level to alter the code if it does not find the word "TOTAL" to make it work.

 

[Fluff]

How about

Sub Edgarvelez()
   Dim Fnd As Range
   
   Set Fnd = Sheets("Upload Sheet").UsedRange.Find("Total", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.Resize(, 6).ClearContents
End Sub

 

[Edgarvelez]

I gave it a try and I increased the resize to 7, I think we are close.

   Dim Fnd As Range
   
   Set Fnd = Sheets("Upload Sheet").UsedRange.Find("Total", , , xlWhole, , , False, , False)
   If Not Fnd Is Nothing Then Fnd.Resize(, 7).ClearContents

I cant use ClearContents because the line has autosum and the format is different and when I run the code for the second time to add more rows things get a little out of format, I am think if we use select and then delete then that might work.

 

[Fluff]

Do you want to delete the entire row?

 

[Edgarvelez]

Correct.

 

[Edgarvelez]

if the line is deleted the when I run the code that puts all that data it will work.
I currently do it manually I select the whole row and then right click and Delete.

 

[Fluff]

In that case use

If Not Fnd Is Nothing Then Fnd.EntireRow.Delete

 

[Edgarvelez]

Awesome it worked beautifully.
Thank you very much.

 

[Fluff]

You're welcome & thanks for the feedback.