Stop delete range deleting "total" cells

[AnnieS2Elise]

Hi all

Can anyone tell me what's wrong with my code?

I'm trying to get a range to start with a set number of rows when I click a button, but not delete the "Ad_Total" row.

It needs to work whether I'm starting with 6 or 20 rows and always end up with 5 rows in a range, which the "Ad_Date" cell above and "Ad_Total" cell below.

Currently if I click my button enough times it deleted the "Ad_Total" row.

Thanks in advance
Annie

[TABLE="width: 72"]
<tbody>[TR]
[TD="width: 72, bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Private Sub  New_Button_Click()[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim Answer As String[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim MyNote As String[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim iStartRow1 As Integer[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim iEndRow1 As Integer[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]iStartRow1 =  Range("Ad_Date").Row + 6[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]iEndRow1 =  Range("Ad_Total").Row - 2[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]MyNote = "Changing to a new month  will delete all current data. Have you saved a copy of your file?"[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Answer = MsgBox(MyNote, vbQuestion +  vbYesNo, "STOP!")[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]If Answer = vbNo Then[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Unload Me[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim List As Variant[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim LR As String[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Dim r As Long[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]List = Array("Ad_Total")[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]LR = Range(iStartRow1 &  ":" & iEndRow2)[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]If  IsError(Application.Match(Range("Ad_Total").Text, List, False))  Then[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Unload Me[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Else[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Rows(iStartRow1 & ":"  & iEndRow1).Delete[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]Range("Ad_Contents").ClearContents[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End If[/COLOR][/SIZE][/FONT][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][FONT=Calibri][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT][/TD]
[/TR]
</tbody>[/TABLE]

 

[Anthony47]

Re: Stop delete range deleting "total" cells - URGENT

Can anyone tell me what's wrong with my code?

I seem you already answered this question: "It doesn't work"

To come with a good suggestion, however, you should explain which is the need.

So try explaining how your data are organized and what you wish to achieve.

Bye

 

[AnnieS2Elise]

Re: Stop delete range deleting "total" cells - URGENT

Ciao Anthony47

Esempio e spiegazione di seguito.

Grazie

(Example and explanation below for the non Italians here)

Starting point:

[/IMG]

I then run a macro to set the rows in that range back to 5visible rows (and one hidden)

[/IMG]

If I ‘accidentally’ run the macro again, this happens. I wantsomething in the code that will say if the range includes “Ad_Total” cell (whichis the total cell), don’t run the macro

[/IMG]<strike></strike>

<strike></strike>

 

[Anthony47]

Re: Stop delete range deleting "total" cells - URGENT

You use named ranges to point the crucial area of your worksheet; we (Italians, Germans, Americans) don't know their position, but I guess that you can play with iStartRow1 and iEndRow1 to check if the macro should be run or not. Something like:

iStartRow1 = Range("Ad_Date").Row + 6
iEndRow1 = Range("Ad_Total").Row - 2
'Check if enough space:
If (iEndRow1 - iStartRow1) < 5 Then       '<<< The "right" limit
    MsgBox ("Nothing to remove")
    Exit Sub
End If
MyNote = "Changing t... etc etc"

Bye

 

[AnnieS2Elise]

Re: Stop delete range deleting "total" cells - URGENT

You use named ranges to point the crucial area of your worksheet; we (Italians, Germans, Americans) don't know their position, but I guess that you can play with iStartRow1 and iEndRow1 to check if the macro should be run or not. Something like:

iStartRow1 = Range("Ad_Date").Row + 6
iEndRow1 = Range("Ad_Total").Row - 2
'Check if enough space:
If (iEndRow1 - iStartRow1) < 5 Then       '<<< The "right" limit
    MsgBox ("Nothing to remove")
    Exit Sub
End If
MyNote = "Changing t... etc etc"

Bye

Works perfectly - thank you!!!!

 

[Anthony47]

Re: Stop delete range deleting "total" cells - URGENT

Grazie per il feedback