VBA coding assistance to define row number from a cell ref

[Data Monkey]

I have a piece of code to delete out rows but I need the range to be dynamic.

Currently my code is as follows but I need to change the "4" to be a cell reference as I have a formula that decides where to start the delete from.

Please help!

<Code>
Sub Macro1()
'
' Macro1 Macro
'


'
ActiveWorkbook.Worksheets("Position Selection").ListObjects("Table7").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Position Selection").ListObjects("Table7").Sort. _
SortFields.Add Key:=Range("Table7[[#All],[Customer]]"), SortOn:= _
xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Position Selection").ListObjects("Table7").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
With Worksheets("Position Selection")
Rows("4:" & .Rows.Count).Delete
End With
End Sub
</Code>

 

[Caleeco]

I suppose your line of code would change to

Rows(ActiveCell.Row & ":" & .Rows.Count).Delete

So, all the rows from the selected cell to the end of the sheet will be deleted.

Is this what you intend your code to do?

Regards
Caleeco

 

[Data Monkey]

It's not necessarily the active cell though.

if cell M2 = 135 then I would want all rows deleting after 134

thanks,

 

[Caleeco]

It's not necessarily the active cell though.

if cell M2 = 135 then I would want all rows deleting after 134

thanks,

In which case the line would be

Rows(Range("M2").Value & ":" & Rows.Count).Delete

This would include the row number listed in cell M2.

Hope that helps
Regards
Caleeco

 

[Data Monkey]

Perfect! Thank you. I knew it would be something simple!

 

[Caleeco]

Perfect! Thank you. I knew it would be something simple!

No problem, glad i could help :D

Thanks
Caleeco