entering data into excel using a form

[22021974]

I have an excel spreadsheet with a macro and a form built on it. The form contains information that the user selects from and hits the "enter data" command button which subsequently should add data to the excel spreadsheet. I was able to get past it using a crude approach but it is not fool proof. I would like to know the following with regards to adding the data to excel
Scenario 1: If the spreadsheet is blank, the data should be entered from the second row
Scenario 2: If data is already in the spreadsheet, the macro should append the data to it (detect the next row that is blank and add data to it).
Appreciate your help

 

[lambuhere1]

In the command button code,

try this

lastrow = Range("A65536&quot.End(xlup).row

'this gives you the last row that contains the data. Use this to add your data from form onto the worksheet. Hope this helps.

Ram P

 

[BrazilBill]

Something that I tend to use for this is a very simple function that can be used to derive the total amount of populated rows in a sheet (plus 1).

Define a function i.e:

Public Function TotalRows(sngSheet As Single, Col As Single) As Single
Dim i As Integer
i = 1
Do While Worksheets(sngSheet).Cells(i, Col) <> &quot;&quot;
i = i + 1
Loop
TotalRows = i
End Function

When you call the function from your command button script, you need to pass in the spreadsheet number and the column to check for data i.e:

NextRow = TotalRows(1, 1)

NextRow then stores the next available row number. Simple but pretty useful. Hope this helps.

 

[combo]

Suppose you have table with headers starting in A1, data row starts at row 2.

You can use

[tt] If Sheet1.Range(&quot;A1&quot.CurrentRegion.Rows.Count=1 then
' Scenario 1
Else
' Scenario 2
end If[/tt]

if the code name of sheet with data is Sheet1

 

[22021974]

Thanks to everybody for their speedy response. I will give it a try and let you all know.

 

[22021974]

Ram, Can you also pls let me know why u have
Range(&quot;A65536&quot in the below

lastrow = Range(&quot;A65536&quot.End(xlup).row

Is that the max number of row in an excel spreadsheet.

Thanks, I used your solution and it works great.

 

[lambuhere1]

Yes,

It uses the logic of finding the last filled cell from bottom of the worksheet(max 65536 cells) to find the row number. You can change the alphabet A to anyone which has your data.

Hope this helps you out.

Ram P

 

[22021974]

Ram, Thanks for the quick response. You mentioned i could replace the alphabet A with any other column. The current solution checks only for column A but suppose there is an instance where Column A has no data but column b or column c or for that matter anyother column has data in it, how do we resolve that scenario

 

[xlbo]

Then, as long as you don't have gaps in the 1st few rows - ie headers then continual data, you can use
Usedrange.rows.count + 1
to get the next available row Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[lambuhere1]

You can replace A with any column headers that has the maximun row data

or you can use geoff idea, which ofcourse is new to me.. he can better guide you in this.

Ram P

 

[22021974]

Thanks Geff and Ram, I will give Geff's idea a try. Thanks