If is last row with data (values) in cells then...

[sdoppke]

Hi everyone i have a simple macro that removes some data in a row then copies what is below and moves it all up (so there is not an empty row). Its pretty basic in the sence it just highlights the row that needs to be deleted and deletes the cells in it and then with Cntr and arrow buttons does the rest. ( see my macro below) The problem is if it is the last row it errors out because what is below is 1000's of lines of empy data. Would anyone be able to help me to modify this with an If Else statement that identifies if this is the last row of data?

Here is what im using now:

Sub Delete_Row_20()
Application.ScreenUpdating = False
    Range("B20").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Range("B21").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Range("B20").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.ClearContents
    Range("C2").Select
Application.ScreenUpdating = False
End Sub

I have a button like this in 30 rows (1 for each row) I just need to delete its particular row and move any data below (if there is any) up. I cannot use a simple delete row macro, cause it cause REF#! errors in other sheets.

Thanks everyone in advance.

sd

 

[Sharpefiction]

Im embarrassed !! Im usint "=" formulas

=MyStoreInfo!$B$10&" "&MyStoreInfo!$C$10

Is there a way to still use some thing like so, when i delete a row?

thanks for hanging in there.

sd

Think you're best bet is either the naming the range option, but judging by your formula above, that might not work or tell the macro to refresh your formulas whenever you delete something...

' Sheets("Example").select
' range("B2:B10").value = ""
' range("B2").formula = "=MyStoreInfo!B10&" "&MyStoreInfo!C10"
' range("B2").copy
' range("B2:B10").pastespecial (xlpasteformulas)
' application.cutcopymode = false

It's a bit 'dirty' but it will do the trick...

 

[sdoppke]

Think you're best bet is either the naming the range option, but judging by your formula above, that might not work or tell the macro to refresh your formulas whenever you delete something...

' Sheets("Example").select
' range("B2:B10").value = ""
' range("B2").formula = "=MyStoreInfo!B10&" "&MyStoreInfo!C10"
' range("B2").copy
' range("B2:B10").pastespecial (xlpasteformulas)
' application.cutcopymode = false

It's a bit 'dirty' but it will do the trick...

Dirty works for me!!! worked great!

sd

 

[Sharpefiction]

Dirty works for me!!! worked great!

sd

Happy days! Glad it worked for you..

 

[Comfy]

Another option would be to REplace the = in formulas with a random string prior to deletion and then change it back afterwards?

Sub Macro()
Dim bRow As Long
With Sheet2.UsedRange 
        .Replace "=", "@###@" 
    End With
    bRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    ActiveSheet.Shapes(Application.Caller).Delete
    Rows(bRow).Delete
With Sheet2.UsedRange 
        .Replace "@###@", "=" 
    End With
End Sub