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

 

[Comfy]

You could have one macro for all thirty buttons.

This identifies the row number of the button that was clicked and deletes that button along with the row that it is on.

Sub Macro()
Dim bRow As Long
    bRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    ActiveSheet.Shapes(Application.Caller).Delete
    Rows(bRow).Delete
End Sub

This will only work with a button from the Form Controls, this will not work with a Command button from the ActiveX Controls.

Please backup your data before trying.

 

[sdoppke]

You could have one macro for all thirty buttons.

This identifies the row number of the button that was clicked and deletes that button along with the row that it is on.

Sub Macro()
Dim bRow As Long
    bRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
    ActiveSheet.Shapes(Application.Caller).Delete
    Rows(bRow).Delete
End Sub

This will only work with a button from the Form Controls, this will not work with a Command button from the ActiveX Controls.

Please backup your data before trying.

Comfy, Thanks for your reply. I really like that idea. Is there a way to NOT delte the row? It seems when i delete a row on this sheet it kills vlookups on other sheets.

Also Id like to keep the button as well for future use?

Thanks again.

sd

 

[Comfy]

You could just hide the row?

Sub Macro()
Dim bRow As Long
    bRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
If Rows(bRow).EntireRow.Hidden = False Then Rows(bRow).EntireRow.Hidden = True
End Sub

else I'll add your else if

 

[sdoppke]

You could just hide the row?

Sub Macro()
Dim bRow As Long
    bRow = ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row
If Rows(bRow).EntireRow.Hidden = False Then Rows(bRow).EntireRow.Hidden = True
End Sub

else I'll add your else if

I like the way your think but, I cant hide it cause of the vlookups on other sheets that reference those rows. The data would have to be replaced.

Helps if I tell what the rows are. The rows are names of employees. Often times they are not current, so a name will need to be deleted (then the below names( if any) will need to be moved up, so the vlookups on the other sheets can reference those names.

Thanks sd

 

[Comfy]

Deleting Rows of unused data should not have an adverse effect on vlookups in other sheets as when rows are deleted Excel automatically changes the vlookup Range to the new, in this case, shorter range.

 

[sdoppke]

Deleting Rows of unused data should not have an adverse effect on vlookups in other sheets as when rows are deleted Excel automatically changes the vlookup Range to the new, in this case, shorter range.

When i delete a row on this sheet, the vlookup referencing that row on the other sheets get a #REF! error. Can you think of something I am doing wrong?

 

[Comfy]

Could you paste some examples of you vlookups so that I can see how you have your sheets setup.

 

[Sharpefiction]

A possible way around the #ref error you're getting on the other sheets could be to name the range you're looking up...<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>


For instance I'd consider:<o></o>

• Naming range A1 to A10 (or whatever your cells are that contain data you want removing), for example as 'colleaguenames'<o></o>
• Refer your vlookups to the named range
  for example: vlookup(A1,Colleaguenames,1,false)<o></o>
• When you delete a row the named range will update automatically and shrink, so to re-size the range, I’d find the last row of data and then reset the named range every time the deletion prompt was done
  For example: FinalRow = cells(rows.count,1).end(xlup).row
  range(“A1:A”& FinalRow).name = “colleaguenames” <o></o>

Might be others ways to do it, but it’ll mean you don’t get #ref errors and you’ll have dynamic ranges<o></o>
Does that make sense?<o></o>

 

[sdoppke]

Could you paste some examples of you vlookups so that I can see how you have your sheets setup.

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