Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: insert data in new row
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: insert data in new row
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(r w)) = 0 Then GetLastRow = rw Exit Function End If rw = rw + 1 Loop End Function "sam" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: insert data in new row
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 -- HTH... Jim Thomlinson "sam" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: insert data in new 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? "Patrick Molloy" wrote: 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(r w)) = 0 Then GetLastRow = rw Exit Function End If rw = rw + 1 Loop End Function "sam" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
HELP: insert data in new row
yes - I gave you the code in my response ....
"sam" wrote: 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? "Patrick Molloy" wrote: 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(r w)) = 0 Then GetLastRow = rw Exit Function End If rw = rw + 1 Loop End Function "sam" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy all data into one sheet and insert origin data... code amend... | Excel Programming | |||
MULTIPLE DATA - How to insert new data into existing data.... | Excel Discussion (Misc queries) | |||
insert entire row for new data, external data range doesnt work | Excel Programming | |||
Use excel to insert data at the bottom of existing data | Excel Programming | |||
Import Data: on insert, shift data down and not right | Excel Discussion (Misc queries) |