Excel and VB Script help

[etzina]

hi guys, gipa himo mi ug form (in an excel worksheet) and we based our vb script from this site: Excel - adding data to a list (part two)

what it basically does is that we have sheet1 as the form, and sheet2 as the table of data that has been typed on sheet 1.

we have already accomplished this, any info typed in sheet 1, when the macro is run, mu append sa sheet 2 list ang data.

now here's the problem... If we delete a row or any info sa sheet 2, the counter amo gisetup dili mu change... so example... on sheet 2, rows 1 til 5 have info already... then we delete info on row 4.. if we type in on sheet 1 form, and run the macro, the data is then appended on row 6, not on row 4...

naa mo script na makatabang ug check na sheet2 table row if empty diha sya mu append sa data? galibog ko coz dugay2x nako la nakaatubang ug vbscript gud...

ty .. i hope na explain ra nako unsa ako gi mean

[dlanyer77]

Private Sub Button2_Click()
Dim NewRow As Integer
'NewRow = Worksheets("altmain").Range("D7").Value + 1 'your old code
NewRow = Worksheets("table").Range("A65536").End(xlUp).Row + 1 'my solution
If Worksheets("altmain").Range("C6").Value <> 0 Then
MsgBox "There are errors. No data has been added!", vbOKOnly, "MeadInKent"
Exit Sub
End If

Worksheets("table").Cells(NewRow, 1).Value = Worksheets("altmain").Range("B3").Value
Worksheets("table").Cells(NewRow, 2).Value = Worksheets("altmain").Range("B4").Value
Worksheets("table").Cells(NewRow, 3).Value = Worksheets("altmain").Range("B5").Value
MsgBox "New Data added", vbOKOnly, "MeadInKent"
Worksheets("altmain").Range("B3").ClearContents
Worksheets("altmain").Range("D7").Value = NewRow
Worksheets("altmain").Range("B3").Select
End Sub

have a nice day!

[etzina]

ty for trying, but it doesn't work....

[dlanyer77]

ty for trying, but it doesn't work....

Paste this code to your "table" Worksheet

Private Sub Worksheet_Deactivate()
Worksheets("altmain").Range("D7").Value = Worksheets("table").Range("A65536").End(xlUp).Row
End Sub

If it won't work. Consider sending your file to be more precise.

[The_Undertaker]

since mag delete man ka in between data of rows... you could try including a counter in your macro which will traverse the number of rows with vaues in it. the counter will continue to loop until an empty cell has been reached...

counter = 1

do
counter = counter + 1

loop while not (thisworkbook.worksheets("Sheet1").cells(counter + 3, 1).formulaR1C1 = empty)


the above sample assumes u've started ur search at row 4 (sample only)

(there are many other ways actually, im not really sure is this is what u need but i hope it helps)

[cres]

hi guys,pwde me patabang..kabalo mo unsaun pagbuhat og recycle bin using visual basic 6.0...tnx

[MarkCuering]

all you have to do is to scan the empty rows on sheet2, and fill those empty once. eg. cell.Value <> Empty Or cell.Value, or if you are lazy to scan, just directly delete the empty rows on sheet2.