![]() |
#4
|
|||
|
|||
![]()
Did you have a look at my reply to your question here?
You can calculate the last row of the table, the input box is not necessary. It only gives the user a chance to increase the difficulty for you to program against them screwing things up. (Try entering the wrong number, a decimal number or the word "hello") What is the goal here? It appears you want the added column and row to appear to be part of the table without being part of the table. What are you actually trying to do? and why? If the column you're adding is always immediately right of the table, like the row being immediately below the table, no input boxes are required. Code:
Sub Add_Row_and_Column() Dim oLo As ListObject Dim StrtRow As Long, EndRow As Long Dim StrtCol As Long, EndCol As Long Dim i As Integer With ThisWorkbook.Sheets("Sheet1") Set oLo = .ListObjects(1) '<~~ first table on sheet 'establish table location With oLo StrtRow = .ListRows(1).Range.Row EndRow = .ListRows.Count + StrtRow - 1 StrtCol = .ListColumns(1).Range.Row EndCol = .ListColumns.Count + StrtCol - 1 End With 'add column on right of table .Columns(EndCol + 1).Insert For i = StrtRow To EndRow .Cells(i, EndCol + 1).Interior.Color = .Cells(i, StrtCol).DisplayFormat.Interior.Color Next i 'add row below table .Rows(EndRow + 1).Insert .Range(Cells(EndRow + 1, StrtCol), Cells(EndRow + 1, EndCol + 1)).Interior.Color = _ .Cells(EndRow - 1, StrtCol).DisplayFormat.Interior.Color End With End Sub Last edited by NoSparks; 12-22-2016 at 11:04 AM. Reason: added code |
Thread Tools | |
Display Modes | |
|
![]() |
||||
Thread | Thread Starter | Forum | Replies | Last Post |
proper | snoforlife | Excel Programming | 0 | 01-26-2016 02:16 PM |
![]() |
MoiraB | Word | 2 | 08-26-2015 07:13 PM |
Proper Text Format | sufian,naeem | Excel | 1 | 05-05-2014 05:59 AM |
![]() |
choy | Word | 3 | 08-01-2012 09:12 PM |
![]() |
judicial85 | Excel | 1 | 10-23-2011 01:35 PM |