How do I check for the end of a row in Cell O and jump to the next row to Insert a New Record?

Hi Kurt

Is this what you mean ? Note I have used the sheets CodeName as apposed to the Tab name.

Sub TryThis()
Dim LastCell As Long
LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0)
Sheet1.Range.Range("A" & LastCell) = "NewRecord"
End Sub

Dave

OzGrid Business Applications

Thanks for the fast response Dave, but New Record does not work in Excel 97

Hello Dave,

Thanks for your very fast response on a Saturday. I am glad to know some of us are still out there coding!! he he

I am using Excel 97 if that makes any different the code stops on the line of the New Record.

Again thanks for your prompt attention and happy Passover!!

Kurt

Re: Thanks for the fast response Dave, but New Record does not work in Excel 97

Hi Kurt

It is 6 am here in Oz, hence my code typo :o)

Should be this:

Sub TryThis()
Dim LastCell As Long
LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row
Sheet1.Range("A" & LastCell) = "NewRecord"
End Sub

Forgot to put .Row

Dave

OzGrid Business Applications

close and almost there!!

Hello Dave,

I wish I could pour you a cup of coffee or something!!

Yes, the Row helps, but I am trying to keep what is in cell A and have the cell pointer move down to the next blank line in the database.

Thanks again,

Kurt

Re: close and almost there!!

It's ok Kurt, I now have a coffee!

Sub TryThis()
Dim LastCell As Long
LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row
Range("A" & LastCell).Select
End Sub

This will select the same row in column A as the last row+1 in Column O

Dave

OzGrid Business Applications

Re: close and almost there!!

Hello Dave,

Would you please take a look at the email I sent you. Again thanks for your patience and help.

Kurt

Re: close and almost there!!

Kurt, I'm not at that address today, so could you send it to dhawley@micl.com.au

Dave
OzGrid Business Applications

Re: close and almost there!!

Hello Dave,

I tried to send it there but my aol account says it doesn't exist so here is the total code. It may look ugly since I am experimenting.

Private Sub CommandButton1_Click()
' Select cell b3, *first line of data*.
Sheets("Patient Info").Select
Range("B3").Select

' This code copies the patient data
Sheets("Patient Info").Select
Range("B3:B17").Select
Selection.Copy
Sheets("Patient Names").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
Application.CutCopyMode = False

''Sub TryThis()
''Dim LastCell As Long
''Dim i As Long
''LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row
''Range("A" & LastCell).Select

Dim LastCell As Long
LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row
Sheet1.Range("A" & LastCell) = "NewRecord"


''Range("A" & LastCell).Offset(0, 1).Select
''For i = Selection.Rows.Count To 1 Step 1

''If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
'' ActiveCell.Select
''Selection.Rows(i).EntireRow.Delete
End If
Next i

''Range("A").Offset(1, 0).Select
''LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row
''Offset(1, 0)("A" & LastCell).Select
''ActiveCell.Offset(1, 0).Row
''End Sub


Range("A1").Select
Sheets("Patient Info").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B3").Select
frmPatientName.Hide

End Sub

Thanks,

Kurt

Re: close and almost there!!

''Sub TryThis() ''Dim LastCell As Long ''Dim i As Long ''LastCell = Range("O65536").End(xlUp).Offset(1, 0).Row ''Range("A" & LastCell).Select Dim LastCell As Long LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row Sheet1.Range("A" & LastCell) = "NewRecord" ''Range("A" & LastCell).Offset(0, 1).Select ''For i = Selection.Rows.Count To 1 Step 1 ''If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then '' ActiveCell.Select ''Selection.Rows(i).EntireRow.Delete End If Next i

Kut, you could shorten the first bit to:
'This code copies the patient data
Sheets("Patient Info").Range("B3:B17").Copy
Sheets("Patient Names").Range("A2").PasteSpecial Transpose:=True
Application.CutCopyMode = False

The rest though you will have to tell me what it is you want to do as i cannot tell from looking at it. E.G; you have:

LastCell = Sheet1.Range("O65536").End(xlUp).Offset(1, 0).Row
Sheet1.Range("A" & LastCell) = "NewRecord"

Range("A" & LastCell).Offset(0, 1).Select
For i = Selection.Rows.Count To 1 Step 1

If WorksheetFunction.CountA(Selection.Rows(i)) = 0 Then
ActiveCell.Select
Selection.Rows(i).EntireRow.Delete
End If
Next i

Which is trying to loop through 1 cell in Column B

Dave

OzGrid Business Applications

Re: close and almost there!!

Hello Dave,

Thanks for your patience!! What I am trying to do is to take values that are entered on a sheet called Patient Info in cells B3 to B17 and then copy and paste them onto another worksheet called Patient Names.

I would like the cell pointer to recognize when there is a new blank row and paste the next name copied form Patient Info to Patient Names every time.

I hope this helps clarify things a bit :-)

Thanks,

Kurt