Thank you so much for helping me out.
I put this in my example spreadsheet, changed
a few values to set the correct Data fileds,
but it does not seem to do anything.
(I am not sure if the OPEN EVENT is even being
activated. )
It should be adding a blank row if I type information
in anywhere on a blank line, and deleting any blank ones,
unles it is the last one.
I thought maybe seeing what I am doing might help, I have
put a copy of a sample spreadsheet on this page :
http://www.geocities.com/rockytophubby/Example.html
Would you mind looking it over for me? This my first attempt at making
an excel spread sheet with VBA, and I can only hope future attempts
will become easier for me LOL (You are helping me learn it by leaps
and bounds :-)
Thanks in advance.....
On Tue, 9 Nov 2004 23:58:19 +1300, "Rob van Gelder"
wrote:
Press Alt+F11 (to get to VBA)
Press F1
Then look up Events / WorkbookOpen Event in the online help.
Generally, to start learning VBA, you'll use Excel's macro recorder to
record simple actions. Then study the generated code to learn Excel's object
model and VBA functions.
VBA is very similar to the Pseudocode you've written below.
Here's the conversion. be warned, I have not tested it - I'm supplying
mostly to demonstrate conversion rather than be accurate to your
requirements.
You put this in the Workbook code module (Doubleclick the ThisWorkbook
object in the Project Explorer)
Option Explicit
Private DataLines As Long
Private Enum eChkRow
Blank
out
Full
End Enum
Private Sub Workbook_Open()
DataLines = 0
Do
DataLines = DataLines + 1
If ChkRow(DataLines + 5) = Blank Then Exit Do
Loop
End Sub
Private Function ChkRow(RTBC As Long) As eChkRow
If ((RTBC < DataLines) Or (RTBC DataLines)) Then
ChkRow = out
ElseIf WorksheetFunction.CountBlank(Range(Cells(RTBC, 2), Cells(RTBC,
6))) = 4 Then
ChkRow = Blank
Else
ChkRow = Full
End If
End Function
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cRow As Long, cCol As Long
Dim CR As eChkRow, NR As eChkRow
cRow = Target.Row
cCol = Target.Column
If (cCol < 2) Or (cCol 6) Then Exit Sub
CR = ChkRow(cRow)
NR = ChkRow(cRow + 1)
If (CR = out Or (CR = Blank And NR = out)) Then Exit Sub
If (CR = Full And NR = out) Then
Application.CutCopyMode = False
With Worksheets("Sheet1")
.Rows(CR + 1).Insert xlShiftDown
.Rows(CR).Copy
.Rows(CR + 1).PasteSpecial xlPasteFormulasAndNumberFormats
End With
Application.CutCopyMode = False
With Worksheets("Sheet2")
.Rows(CR + 1).Insert xlShiftDown
.Rows(CR).Copy
.Rows(CR + 1).PasteSpecial xlPasteFormulasAndNumberFormats
End With
Application.CutCopyMode = False
DataLines = DataLines + 1
ElseIf (CR = Blank And NR = Full) Then
Worksheets("Sheet1").Rows(CR).Delete
Worksheets("Sheet2").Rows(CR).Delete
DataLines = DataLines - 1
End If
End Sub
Good Luck