Problems with row selection in basic

[PaulS]

Hope one of you with more knowledge of VBA can help.
The function of my macro is simple: delete e number of rows from a sheet. This only when macro is activated from the right position. Can you please give advise for the statements of the do-loop:
-------------
Sub Clearsheet()
'
Dim c As Range

'cell selected as variable
Set c = Selection
'cell ALxx contains the row number
'do not execute when activated from wrong position.....
If c.Row <> Cells(c.Row, "AL").Value Then Exit Sub
'now rows number 31 to c.Row -1 must be deleted from sheet
Do I = 31 to c.Row -1
Range(I:I).Select
EndDo
'All rows to be deleted are selected
Selection.Delete Shift:=xlUpEnd
End Sub
-------------
Thanks for your help
Paul

 

[texasalynn]

change your macro to delete as it finds the rows

Sub Clearsheet()
Dim c As Range

'cell selected as variable
Set c = Selection
'cell ALxx contains the row number
'do not execute when activated from wrong position.....
If c.Row <> Cells(c.Row, "AL").Value Then Exit Sub
'now rows number 31 to c.Row -1 must be deleted from sheet
For I = 31 to c.Row Step -1 
Range(I:I).Delete Shift:=xlUpEnd 'row to be deleted
Next I
End Sub

 

[PaulS]

Thanks sofar. It looks like this is 99%.
A compile error occurs at statement in loop. Error on Range(I:I)......
When code has been entered, the I at the For and the one at the Next change into small i - not the one in the Range(I:I)...?? Reason?

 

[texasalynn]

I see - the range needs the I's in quotes

Range("I:I")

 

[PaulS]

Sorry, the For-loop still does not work.
Short explanation of the function:
The worksheet holds the cash transactions of our threasurer. At the end of a period the old transactions can be removed. The last transaction row has the row number in col AL. Only when macro executed from this line it may execute its function. This macro will delete all rows from 31 to the active row -1.
I can reasonably work with excel, but lack enough knowledge of VBA macro writing.
My suggestion was the selection of all rows in the For-loop, and thereafter execute the delete statement. I started to try this with a recorded macro, but the selection statement is getting too big when approx 200 lines have to be deleted.
Thats the resaon for my request for help on this problem.
Thanks,

 

[texasalynn]

ok so is the activecell where you want the macro to stop? And I think a slight modificaiton like this will do

Sub Clearsheet()
Dim c As Range
LR = ActiveSheet.Cells(Rows.Count, "AL").End(xlUp).Row
'cell selected as variable
Set c = Selection
'cell ALxx contains the row number
'do not execute when activated from wrong position.....
'If c.Row <> Cells(c.Row, "AL").Value Then Exit Sub
'now rows number 31 to c.Row -1 must be deleted from sheet

For I = LR To c.Row Step -1
    Rows(I).Delete Shift:=xlUpEnd 'row to be deleted
Next I
End Sub

I commented out the If c.Row line as that seems to be an issue, unless the selected row number will be equal to the value in the active cell.

HTH

 

[PaulS]

This works! Many thanks,
Paul