Add/Remove rows with formatting Macro

The code below will cycle through the sheets of the workbook and delete the row based on the user input.

Sub DeleteUserRows()
'You might want to turn off the screen updating to make it go quicker and just look better.
Application.ScreenUpdating = False

'Get the user to input the row to delete
Dim uRow As Integer
uRow = InputBox("Please type the row to delete")

'cycle through the sheets in the workbook and delete the row specified
For i = 1 To ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets(i).Rows(uRow).Delete
Next i

Application.ScreenUpdating = True
End Sub

mike72384 said:

The code below will cycle through the sheets of the workbook and delete the row based on the user input.

Sub DeleteUserRows()
'You might want to turn off the screen updating to make it go quicker and just look better.
Application.ScreenUpdating = False

'Get the user to input the row to delete
Dim uRow As Integer
uRow = InputBox("Please type the row to delete")

'cycle through the sheets in the workbook and delete the row specified
For i = 1 To ThisWorkbook.Sheets.Count
ThisWorkbook.Sheets(i).Rows(uRow).Delete
Next i

Application.ScreenUpdating = True
End Sub

Click to expand...

Thanks Mike, that was exactly what I was looking for! Though I now realize I forgot that I have an instructions sheet as the first tab. Is there an easy way to exempt that sheet?

Just define the Last Sheet like Mike has done above (ThisWorkbook.Sheets.Count) but in order to skip the 1st sheet change this line:



To


You may want to check in the VBE project explorer to make sure that the Sheet that you say is the first sheet is identified as "Sheet1." If it isn't the code will skip a different sheet then you anticipate.

You can also and an if statement like:

if ThisWorkbook.Sheets(i).Name <> "TheSheetYouDon'tWantName" then

end if

This will make it so the operation only happens in sheets without the name of the instructions/title/index or whatever sheets are not data. This is a solution in case the sheet is not actually sheet #1, since the flag is the name, not number.

Ah, so simple starting on 2 instead of 1. Not sure why I didn't think to try that. Instructions is sheet 1 and I see no reason for that to change if I have any say about it!

The delete macro works great and does exactly what I want, as long as I don't hit cancel or close the dialog box. If I do that I get a type mismatch error... Thoughts?

I really appreciate the help, my forehead is soar from banging against my keyboard trying to figure this stuff out!

Since you dimensioned the uRow variable as an integer, if you cancel or close the input box it will default to a 0 value, rather than an empty string.

At the top of the code put: (This will make the code bypass the error message)
On Error Resume Next

Then put in this line after: uRow = InputBox("Please type the row to delete")
if uRow = 0 then exit sub

This should cancel the code if the user does not put anything, hits cancel, or closes the window via the X in the top right corner.

mike72384 said:

Since you dimensioned the uRow variable as an integer, if you cancel or close the input box it will default to a 0 value, rather than an empty string.

At the top of the code put: (This will make the code bypass the error message)
On Error Resume Next

Then put in this line after: uRow = InputBox("Please type the row to delete")
if uRow = 0 then exit sub

This should cancel the code if the user does not put anything, hits cancel, or closes the window via the X in the top right corner.

Click to expand...

Works perfectly, thank you! ray:

You wouldn't happen to have any ideas on how to add rows across all the sheets above the button and including the formatting would you?

I am not fully sure what you mean by "Above the button", but you can insert a row (just like the code deleted them above) by the command:

ThisWorkbook.Sheets(i).Rows(uRow).Insert

This will insert a row above row "uRow" on Sheet "i"

I am also not 100% clear on where you want the formatting to come from, but in general you can use the ".copy" action on whatever cell/range you want the formatting from and the ".PasteSpecial (xlPasteFormats)" applied to wherever you want to paste.

I believe you might have to activate whatever sheet you are pasting to otherwise the paste command might give you an error, but you can do that with "Sheets(i).Activate"

Hopefully that clears it up enough, but if not, you could give more specifics on where the formatting is coming from and going and I could help you set up a range variable to define that in the VBA code if needed.

I'm pretty new to VBA codes and macro's but I think I understand what you're saying. Just in case I'll try and clarify what it is I'm trying to do. There are three different sections of different paints (paints, thinners, spray cans) that a user may need to add a line to as new paints are used. All the information about the paint (part #, paint name, VOC content, Emissions) are all driven off of the master sheet. The idea being that when a new paint is added, or an existing one modified, it only has to be changed in one location. So when a new lines inserted I need the formula to be pulled down from the row above. Additionally I have a table in each section that would also need to be expanded to the newly created row.

So far I found this code:
Dim r As Range
Set r = ActiveSheet.Buttons(Application.Caller).TopLeftCell
r.EntireRow.INSERT

It adds a row above the button, but only on one sheet and doesn't include any formatting. But there may be a better/cleaner way.

Hopefully that clears it up a bit and I didn't lose you....