deleting range macro

[daveyc18]

right now i look for "TSYHLD" and delete that row and all the rows it above until row 6

Cells.Find(What:="hld", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Range("A6:AK1189").Select
    Selection.Delete Shift:=xlUp

so TSYHLD will not always be on row 1189...it possible to do a dynamic range so delete the TSYHLD row and all rows until row 6 no matter which row TSYHLD will be on?

 

[Rick Rothstein]

right now i look for "TSYHLD" and delete that row and all the rows it above until row 6

Cells.Find(What:="hld", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Range("A6:AK1189").Select
    Selection.Delete Shift:=xlUp

so TSYHLD will not always be on row 1189...it possible to do a dynamic range so delete the TSYHLD row and all rows until row 6 no matter which row TSYHLD will be on?

What is TSYHLD and how is it related to the cell containing "hld"?

Is the row that finding "hld" on what you want to replace the 1189 with?

 

[daveyc18]

What is TSYHLD and how is it related to the cell containing "hld"?

Is the row that finding "hld" on what you want to replace the 1189 with?

yes..sorry shouldve written "tsyhld" in the macro to be safer
...

also AFTER deleting the rows....i want to find "RULEID" then the macro to delete that ENTIRE ROW (no speicfic range required) and all the ones below it

 

[Rick Rothstein]

yes..sorry shouldve written "tsyhld" in the macro to be safer
...

also AFTER deleting the rows....i want to find "RULEID" then the macro to delete that ENTIRE ROW (no speicfic range required) and all the ones below it

Does this macro do what you want...

Sub DeleteAboveTSYHLDandBelowRULEID()
  On Error Resume Next
  Range("A6", Cells.Find("tsyhld", , xlFormulas, xlPart, , , False, False)).EntireRow.Delete
  Range(Cells.Find("ruleid", , xlFormulas, xlPart, , , False, False), Cells(Rows.Count, "A")).EntireRow.Delete
  On Error GoTo 0
End Sub

 

[daveyc18]

Does this macro do what you want...

Sub DeleteAboveTSYHLDandBelowRULEID()
  On Error Resume Next
  Range("A6", Cells.Find("tsyhld", , xlFormulas, xlPart, , , False, False)).EntireRow.Delete
  Range(Cells.Find("ruleid", , xlFormulas, xlPart, , , False, False), Cells(Rows.Count, "A")).EntireRow.Delete
  On Error GoTo 0
End Sub

yes sir...you're a genius

 

[daveyc18]

Does this macro do what you want...

Sub DeleteAboveTSYHLDandBelowRULEID()
  On Error Resume Next
  Range("A6", Cells.Find("tsyhld", , xlFormulas, xlPart, , , False, False)).EntireRow.Delete
  Range(Cells.Find("ruleid", , xlFormulas, xlPart, , , False, False), Cells(Rows.Count, "A")).EntireRow.Delete
  On Error GoTo 0
End Sub

actually seems to be one issue...it does delete the first "ruleID" below...but i should have mentioned that are more "ruleID" headers in the rows below....i need "ruleID" and EVERYTHING below it to be deleted

 

[Rick Rothstein]

actually seems to be one issue...it does delete the first "ruleID" below...but i should have mentioned that are more "ruleID" headers in the rows below....i need "ruleID" and EVERYTHING below it to be deleted

When you say everything below it, do you mean "in all columns"? If yes, how does that figure in with the other word (tsyhld) that you wanted to delete on?

 

[daveyc18]

When you say everything below it, do you mean "in all columns"? If yes, how does that figure in with the other word (tsyhld) that you wanted to delete on?

here's what i mean:

row 6

a
b
c


tsyhld

tsyinfo

ruleid


a
b

ruleid

a
b

i want row tsyhld deleted and everything ABOVE (including row 6)....want "tsyinfo" (multiple rows of information for tsyhld) to remain intact....want everything BELOW the tsyinfo deleted...when a new "ruleid" starts...it's no longer tsyinfo

 

[Rick Rothstein]

i want row tsyhld deleted and everything ABOVE (including row 6)....want "tsyinfo" (multiple rows of information for tsyhld) to remain intact....want everything BELOW the tsyinfo deleted...when a new "ruleid" starts...it's no longer tsyinfo

In your original message, you said "right now i look for "TSYHLD" and delete that row and all the rows it above until row 6". To the best of my understanding, that means Rows 1:5 remain untouched. As far as I can tell, my code does what you are asking for... Row 6 down to the row with "tsyhld" on it are deleted, the row with "ruleid" and all rows below it are deleted and the only thing that remains is Rows 1:5 and whatever rows were between (but not including) "tsyhld" and "ruleid". What is it that you think I am not doing?

 

[daveyc18]

In your original message, you said "right now i look for "TSYHLD" and delete that row and all the rows it above until row 6". To the best of my understanding, that means Rows 1:5 remain untouched. As far as I can tell, my code does what you are asking for... Row 6 down to the row with "tsyhld" on it are deleted, the row with "ruleid" and all rows below it are deleted and the only thing that remains is Rows 1:5 and whatever rows were between (but not including) "tsyhld" and "ruleid". What is it that you think I am not doing?

exactly...but the code doesnt seem to delete eveyrhting below "ruleid".....as i tried to clarify, there are more than one "ruleid" below...i think the code just deletes the "first" ruleid it finds and all of its info...but doesnt delete the other "ruleids" below

row 6

a
b
c


tsyhld

tsyinfo

ruleid


a
b

ruleid

a
b

ruleid

a
b

second ruleid doesnt get deleted