You are here:

Excel/row by row worksheet fill via Userform (VBA)


Dear Bob Umlas

What a great way to contact an expert via AllExperts.
I have a question involving basic VBA in Excel.
I started reading about VBA 3days ago.

Using a self defined Userform, I want to fill a worksheet with data starting from A10: row by row upon clicking the OK button.
Iíve found code that does the trick...

Private Sub cmdOK_Click()
   Dim RowCount As Long
   RowCount = Worksheets("InvulVelden").Range("A10").CurrentRegion.Rows.Count
   With Worksheets("Invulvelden").Range("A10")
       .Offset(RowCount, 0).Value = Me.txtRoomNr.Value
       .Offset(RowCount, 2).Value = Me.cboDrawType.Value
   End With
End Sub

...but only if the entire row is empty.

Column2 in the sheet is not filled via the Userform, it contains a formula to generate a value using the user-provided RoomNr and a automated serial number -> e.g. 1.1, 1.2,  2.1, 2.2, 2.3,  3.1 etc.

However, the formula in column2 in the worksheet causes the next data set to move 7 rows down, instead of only 1. An entry in a cell in the range that is affected by the Offset commands seems to mix up the row by row filling property of this code.

How can I make the VBA-code look only in columnA for the next empty cell to start filling from the first cell in that row.

Hope this is short but clear
Thanks in advance
HJ Rietberg

instead of using
RowCount = Worksheets("InvulVelden").Range("A10").CurrentRegion.Rows.Count
you can use
then instead of
With Worksheets("Invulvelden").Range("A10")
      .Offset(RowCount, 0).Value = Me.txtRoomNr.Value
      .Offset(RowCount, 2).Value = Me.cboDrawType.Value
  End With
With Worksheets("Invulvelden").cells(NextRow,1)
      .Value = Me.txtRoomNr.Value
      .Offset(, 2).Value = Me.cboDrawType.Value
End With
About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here


All Answers

Answers by Expert:

Ask Experts


Bob Umlas


I`m a Microsoft Excel MVP (Most Valuable Professional) and have been since the inception of the program in 1995. I can answer every kind of Excel question except: API, Importing/exporting to other programs (powerpoint, word,...) Also check out my in-person training link at


Worked with MS Excel since version 0.99 (on the Mac!). Was contributing editor to Excellence Magazine, having written >300 articles. John Walkenbach said of me "I finally met someone who knows as much about Excel as I do."

Excellence, The Expert, Microsoft

BA in math, Hofstra University, 1965

Awards and Honors
Led sessions for the Convergence 2004-2006 seminar on Excel tips & tricks

©2016 All rights reserved.