0

I currently have a userform which outputs data into a sheet. I have recently added a table in the output region where my formulas that analyze the data are located, and made it 1000 rows high so that I have space. Now, annoyingly, the data is outputting at the very bottom of the table, not in the first row (I think Excel is interpreting the presence of the table as being a full row and then going below it). I tried just deleting the intervening empty rows, and that works as long as the first row is never deleted. Unfortunately, this will be a form which is cleared and re-filled each day, so I'd rather not require the user to keep the top row at all times.

My current code is the below - how do I change it to ask it to output into the first row of Table2 instead of the first available row of Sheet1?

Private Sub Adddatabutton_Click()

Dim iRow As Long

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

' A bunch of output code

' A bunch of code that clears the form

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Medconfused
  • 1
  • 2
  • The first cell of the table is the `.DataBodyRange.Cells(1, 1)`. – BigBen Jul 09 '20 at 17:22
  • If you need to find the first available row in a table, see [this](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table). – BigBen Jul 09 '20 at 17:23
  • @BigBen Sorry, I'm new to this. Where would I put the .`DataBodyRange.Cells(1,1)` in the code above? Would it go in as `iRow = ws.DataBodyRange.Cells(1,1).End(x1Up).Offset(1,0).Row` ? – Medconfused Jul 11 '20 at 17:44
  • Can you share a bit more of your code, specifically the output code? – BigBen Jul 11 '20 at 18:56
  • @BigBen Sure! It just goes `ws.Cells(iRow, 1) = Me.Textbox1.Value ws.Cells(iRow, 2) = Me.textbox2.Value ws.Cells(iRow, 3) = Me.checkbox1 ws.Cells(iRow, 4) = Me.checkbox2` and then at the end I clear the data using `Me.Textbox1.Value = "" Me.textbox2.Value = "" Me.checkbox1 = FALSE Me.checkbox2 = FALSE` etc. – Medconfused Jul 13 '20 at 13:41
  • See the linked question (in my second comment) for how to find the first available row in the table then. – BigBen Jul 13 '20 at 13:44
  • `iRow = ws.ListObjects("Table1").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row + 1`. – BigBen Jul 14 '20 at 14:42

0 Answers0