HELP: insert data in new row

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

Hi All,

I want to update a worksheet through excel userform. and want to populate a
new row everytime a user submits the data through the userform.
To do That I have the following line of code:

Set DestRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

But with this line, it populates the new row if there is data in first cell
of the previouis row, If the first cell of the previous row has no data
(other cells have data) then It overrites the data in that row.

Hope I made it clear

Thanks in advance
 
you don't say how many columns you have.
You're using A, but are there any other columns that must always have data.

Set DestRow = GetLastRow

Function GetLastRow() As Long
Dim rw As Long
rw = 1
Do Until rw = Rows.Count
If WorksheetFunction.CountA(worksheets("????").Rows(rw)) = 0 Then
GetLastRow = rw
Exit Function

End If
rw = rw + 1
Loop
End Function
 
If there is one column that is always populated then you would want to use
that coulmn as your bibis for determining where your new row should be
placed. To that end all you need to do is to change the 1 in
..Cells(.Rows.Count, 1)

If you can not count an any column to always be populated then use code
similar to

Public Function LastCell(Optional ByVal wks As Worksheet, _
Optional ByVal blnConstantsOnly As Boolean) As Range
Dim lngLastRow As Long
Dim lngLastColumn As Long
Dim lngLookIn As Long

If blnConstantsOnly = True Then
lngLookIn = xlValues
Else
lngLookIn = xlFormulas
End If

If wks Is Nothing Then Set wks = ActiveSheet
On Error Resume Next
lngLastRow = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
lngLastColumn = wks.Cells.Find(What:="*", _
LookIn:=lngLookIn, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column
On Error GoTo 0
If lngLastRow = 0 Then
lngLastRow = 1
lngLastColumn = 1
End If
Set LastCell = wks.Cells(lngLastRow, lngLastColumn)

End Function


used like this

dim rngLast as range

set rnglast = lastcell(sheets("Sheet1"))
msgbox rnglast.row
 
I will try and find out if there are any columns that should have data
always, But is there a way to check multiple column cells as compared to just
one?
 
yes - I gave you the code in my response ....

sam said:
I will try and find out if there are any columns that should have data
always, But is there a way to check multiple column cells as compared to just
one?
Click to expand...
 
Back
Top