Add cells to bottom of list when list length is variable

[AirplaneGuy]

I'm trying to add/delete cells to the bottom of an employee list in columns A:V, but NOT the whole row, across all worksheets. List length varies by department, so the bottom row is different for everyone. As people come and go I need to add or delete these rows from the bottom.

Using an input box to allow a user to find the bottom, does the user input need to be a value or range if using Cells(rows.count...

 

[Zot]

Since you want delete only the last cell on specific column, then probably your input would be the column alphabet? Something like this perhaps?

rowLast = ActiveSheet.Range(<column alphabet> & ActiveSheet.Rows.Count).End(xlUp).Row

Next empty row would be rowLast + 1

 

[AirplaneGuy]

Since you want delete only the last cell on specific column, then probably your input would be the column alphabet? Something like this perhaps?

rowLast = ActiveSheet.Range(<column alphabet> & ActiveSheet.Rows.Count).End(xlUp).Row

Next empty row would be rowLast + 1

The <column alphabet> seems to work with a single column but not with a range. It works great to add an entire row but I can't seem to modify it to just those columns.

Sub add_rows()
Dim ws As Worksheet

LastRow = ActiveSheet.Range("C" & ActiveSheet.Rows.Count).End(xlUp).Row + 1
For Each ws In ThisWorkbook.Worksheets
ws.Activate
[B]Rows(LastRow).Select[/B]
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

Next ws

End Sub

 

[Zot]

I'm confused with InputBox you mentioned. If you need to look at every column, then what you want your InputBox for?

You loop every sheet meaning you want to modify each column in every sheet? Please explain. You must have sheet you want to skip I suppose. How the code know you want to add or remove a cell?

 

[AirplaneGuy]

I'm confused with InputBox you mentioned. If you need to look at every column, then what you want your InputBox for?

You loop every sheet meaning you want to modify each column in every sheet? Please explain. You must have sheet you want to skip I suppose. How the code know you want to add or remove a cell?

The data in every sheet is aligned to the same rows. If a new employee starts working here I need to add their data to every sheet as each sheet is a new day in the calendar, but only columns A:V as those have the relevant formulas, and column X:AH contain data that I can't move.

Different departments are using this with different numbers of people so an input box was my idea so they can input THEIR last row but the rows.count).end is a much better alternative I didn't think of, so inputbox is moot. I only need to find the last used row in my employee data and then add a new line under it only in columns A:V. The rows(LastRow).Select works fine, but I can't find a way to modify it and cut it down to the range I need.

 

[Zot]

Not so clear about the column but you do not need to activate sheet or select the range to add. Try this. I have not tested it

Sub add_rows()

Dim LastRow As Long
Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ThisWorkbook.Worksheets
    LastRow = ws.Range("C" & ws.Rows.Count).End(xlUp).row
    ws.Range("C" & LastRow).Offset(1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Next ws

End Sub