Button disappers when macro run with no data?

[dignityy]

Ok here is a strange occurance I need some help with. When I run the following code with no data in the source tabs (yes it makes no sense to run with no data but anyway) my macro button is deleted off the page? This does not happen if the code is run with data. Here is a summary of the code:

'P1 through P'12 are the same, I'm only showing P11 & P12 and the delete code.


'P11
Worksheets("ledger").Range("A1:B2700").Copy
.Range("A27001").PasteSpecial xlPasteValues
Worksheets("1st sort").Range("ay1:bc2700").Copy
.Range("c27001").PasteSpecial xlPasteValues

'P12
Worksheets("ledger").Range("A1:B2700").Copy
.Range("A29701").PasteSpecial xlPasteValues
Worksheets("1st sort").Range("bd1:bh2700").Copy
.Range("c29701").PasteSpecial xlPasteValues

.Activate


'FASTER code to delete rows
.Range("F1").sort Key1:=Range("F1"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
lFirstRow = .Cells.Find(0, .[f65536].End(xlUp), , xlWhole, , xlNext).Row
lLastRow = .Cells.Find(0, , , xlWhole, , xlPrevious).Row
.Rows(lFirstRow & ":" & lLastRow).EntireRow.Delete

End With

Application.CutCopyMode = False

End Sub



Any ideas?

Thanks

 

[SkipVought]

Hi,

What is the WITH Object that your .code is referring to?

Skip,

_[red][/red]

 

[SkipVought]

I'd guess that it's happening here

.Rows(lFirstRow & ":" & lLastRow).EntireRow.Delete

If no data has been copied, then PERHAPSE one of the rows being deleted is the row that is...

YourButton.TopLeftCell.Row

Skip,

_[red][/red]

 

[dignityy]

Skip,

To answer your first question I am not sure what the WITH is refering to, Loomah from this forum offered that entire delete code section to assist me in deleting all rows that had a zero balance. No where else in the code does the with appear. I actually am now curious what it means also?

Is there a way to keep the button from being affected by the delete function?

 

[GlennUK]

Format your button as "Don't move or size with cells" and see what happens.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[dignityy]

That was it, thanks Glenn.

 

[SkipVought]

or use the ClearContents method rather than the Delete method.

Skip,

_[red][/red]

 

[GlennUK]

My pleasure.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.