First empty row in table

[Peltz]

Hello there.

I've written this code to find the last row in a sheet "ws2".

Sub Registrering()Dim ws1 As Worksheet, ws2 As Worksheet
Dim Fra As Long, Til As Long
Dim i As Integer
Dim StRw As Integer, EndRw As Integer


Application.ScreenUpdating = False
Set ws1 = Sheets("Rutine")
Set ws2 = Sheets("Kontakt")
Set Tab = Worksheets("KOntakt").ListObjects("Tab")


i = ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row
MsgBox i

For various reasons i want to insert a table in this sheet, and acctually want to find the first empty row in the table. When setting up a table, it sets up the heading, and then one empty row. However, when running this code it finds the row after the first empty row in the table, NOT the first empty row under the heading.


So the question is: What can I do to find the first empty row in the table?
BTW: In my example the header is placed in collumn 3.

Thanks

 

[MrTeeny]

If the table header is a fixed address you can just search for a blank cell in a range so if your header row is A6

Range("A7:A77").Find(What:="", lookat:=xlWhole).Row will return the next blank cell, or even just take one away from you offset count

i = ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row -1

 

[Peltz]

Thanks for your quick reply
I really liked the tidyness of this one:

i = ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row -1

The code finds the first empty row in the table. However, after filling data in the first row and running the code again, it finds the same row. I think excel register the empty row in the table as an entry.

Ideas?

 

[jim may]

Not sure i'm catching what the problem is but try changing your code row:
i = ws2.Cells(Rows.Count, 3).End(xlUp).Offset(1, 0).Row


to

i = ws2.Cells(Rows.Count, 3).End(xlUp).Row + 1

What happens with that change?

 

[MrTeeny]

Try this link see if it gives you any better methods of achieving your aims for adding data and dealing with tables using VBA

The VBA Guide To ListObject Excel Tables — The Spreadsheet Guru

 

[Peltz]

No, still finds the row after the first emptt row (in a empty table) OR first row after the last empty row in a expanded empty table

 

[MrTeeny]

Maybe it's the Totals row just being empty, as it's an object you'd probably need to use the correct commands to enter your data into it.

[TABLE="width: 757"]
<tbody id="yui_3_17_2_1_1504186010682_1003">[TR]
[TD]Add Row To Bottom of Table[/TD]
[TD]ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:= True[/TD]
[/TR]
</tbody>[/TABLE]

 

[Peltz]

What I want to do is copying a fixed range C22:D22 from another worksheet into the first empty row in the table, and using this formula to archieve this

ActiveSheet.Cells(i, 3).Resize(, 2).Value = ws1.Range("C22:D22").Value

Combining codes with ListObject and worksheet is pretty much out of my league, so I would need some help to archieve this. Im sorry.

 

[Peltz]

Thanks for your help.

[/COLOR][COLOR=#333333]ActiveSheet.ListObjects("Table1").ListRows.Add AlwaysInsert:= True[/COLOR][COLOR=#333333]

THis code will add a new row to my table. However it still leaves the first empty row empty

 

[Peltz]

I found a primitive soulution I can work with:

Sub Registrering()Dim ws2 As Worksheet
Dim LastColumn As Long
Set ws1 = Sheets("Rutine")
Set ws2 = Sheets("Kontakt")


On Error GoTo 10
i = ws2.ListObjects("Table1").Range.Rows.Count
ws2.ListObjects("Table1").DataBodyRange(i - 1, 1).Select
Exit Sub
10: test

Sub test()
Range("B13").Select
End sub()

Thanks for all your input