[Dba-office] Excet Table with VBA
RockySmolin at bchacc.com
RockySmolin at bchacc.com
Fri Dec 18 08:54:11 CST 2015
I've done a of of VBA behind Excel spreadsheets but none of it of my own
invention - I've always used the macro recorder, recorded my keystrokes
for whatever I wanted, then cribbed the resulting code out of the macro
But I always had a row count so when I wanted to add a line I just used
Cells(Row,Col) method. I'm not sure how I would have recorded an 'add
new row' in a macro.
-------- Original Message --------
Subject: Re: [Dba-office] Excet Table with VBA
From: Susan Harkins <ssharkins at gmail.com>
Date: Fri, December 18, 2015 7:37 am
To: Salakhetdinov Shamil <mcp2004 at mail.ru>,
dba-office at databaseadvisors.com
Thanks Shamil -- it's similar to what I ended up with last night. Let's
compare! I think the only real difference is selecting the newly
row. I knew going in that Tables are internally, a list object, but I
anticipated more Table properties and methods, but nope... :) There
should be an Add method that lets you specify (optional) new values for
new rows! I expected that, but didn't find it.
I find Excel's object model confusing and I struggle to find the right
types and objects in VBA.
Thank you for helping! -- Susan H.
Private Sub CommandButton1_Click()
'Enter value into ListPerson column in Table1.
Dim str As String
Dim dbl As Double
Dim rng As Range
Dim oNewRow As ListRow
str = TextBox1.Value
Set rng =
'Handle Match error when match not found.
If Not IsError(Application.Match(str, rng, 0)) Then
dbl = Application.Match(str, rng, 0)
dbl = 0
'If match is found, display duplicate error.
'If match not found, add new record to Table1.
If dbl <> 0 Then
MsgBox "You can't enter duplicate values.", vbOKOnly, "Error"
Set oNewRow = Selection.ListObject.ListRows.Add(AlwaysInsert:=True)
oNewRow.Range.Cells().Value = str
Set oNewRow = Nothing
On Fri, Dec 18, 2015 at 8:38 AM, Salakhetdinov Shamil <mcp2004 at mail.ru>
> Hi Susan --
> Here it's:
> Option Explicit
> Public Sub testAddXlTableRow()
> Dim lst As ListObject
> Set lst = ThisWorkbook.Worksheets("DataValidation").ListObjects("Table1")
> ' add an empty row and seed its cells' values with
> ' this added row index
> Dim listRow As listRow
> Dim cellIndex As Integer
> Set listRow = lst.ListRows.Add(AlwaysInsert:=True)
> For cellIndex = 1 To listRow.Range.Cells.Count
> listRow.Range.Cells(cellIndex) = listRow.Index
> Next cellIndex
> End Sub
> -- Shamil
> >Thursday, December 17, 2015 5:48 PM -05:00 from "Susan Harkins" <
> ssharkins at gmail.com>:
> >I can add a new row to an Excel Table using the following line of code:
> >But for the life of me I can't figure out how to add a value to the new
> >I've read all day and I can find all kind of articles on the above - but
> >nothing on how to add a value to that new row.
> >Susan H.
> >Dba-office mailing list
> >Dba-office at databaseadvisors.com
> Dba-office mailing list
> Dba-office at databaseadvisors.com
Dba-office mailing list
Dba-office at databaseadvisors.com
More information about the Dba-office