I have done some programming, but never any VBA in excel. I am needing
some help writing 2 ON EVENTS and 1 Function listed below. The logic
works, but the commands and Syntax are what I need help with.
ANY help would be greatly appreciated :-)


_______________On OPEN EVENT_____________________
This will tell me how many lines of data are on the sheet
when it is opend. (The + 5 is to allow for my headers)
Calls to the Function below.

ON OPEN EVENT()
Set DATALINES = 0
STart Loop
Datalines = Datalines + 1
IF CHKROW(DATALINES+5) = BLANK Then END
ELSE LOOP

_______________FUNCTION__________________

This Function returns the Status of the Row sent to it
and Returns Either FULL BLANK OR OUT


FUNCTION CHKROW(RTBC (ROW TO BE CHECKED)

IF ((RTBC < DATALINES) or (RTBC DATALINES)) THEN RETURN OUT
IF COUNTBLANK(RTBC 2:RTBK 6) = 4 RETURN BLANK
Else Return FULL
END FUNCTION

_______________ON CHANGE EVENT___________________

This Will check if the ROW Data was just changed is in the Data
field Range and adds or removes rows.(Calls on Function CHKROW Above)

ON EVENT CHANGE(
SET CROW = Row of the cell data changed in
SET CCOL = Collumn of the cell data changed in

IF (CCOL < 2) or CCOL 6 ) Then END
CR = CHKROW(CROW)
NR = CHKROW(CROW +1)
IF (CR=OUT or (CR=BLANK AND NR=OUT)) THEN END
IF (CR=FuLL AND NR=OUT) THEN
Copy ROW CR on Sheet1
Insert it at ROW CR+1 on Sheet1
Copy ROW CR on Sheet2
Insert it at ROW CR+1 on Sheet2
(Only copy Formating and Formulas not Values)
DATALINES=DATALINES + 1
END
If (CR=BLANK AND NR=FULL) THEN
DELETE ROW CR on SHeet1
DELETE ROW CR on SHeet2
DATALINES=DATALINES - 1
END

END EVENT

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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Dean Goodmen" wrote in message
...
I have done some programming, but never any VBA in excel. I am needing
some help writing 2 ON EVENTS and 1 Function listed below. The logic
works, but the commands and Syntax are what I need help with.
ANY help would be greatly appreciated :-)


_______________On OPEN EVENT_____________________
This will tell me how many lines of data are on the sheet
when it is opend. (The + 5 is to allow for my headers)
Calls to the Function below.

ON OPEN EVENT()
Set DATALINES = 0
STart Loop
Datalines = Datalines + 1
IF CHKROW(DATALINES+5) = BLANK Then END
ELSE LOOP

_______________FUNCTION__________________

This Function returns the Status of the Row sent to it
and Returns Either FULL BLANK OR OUT


FUNCTION CHKROW(RTBC (ROW TO BE CHECKED)

IF ((RTBC < DATALINES) or (RTBC DATALINES)) THEN RETURN OUT
IF COUNTBLANK(RTBC 2:RTBK 6) = 4 RETURN BLANK
Else Return FULL
END FUNCTION

_______________ON CHANGE EVENT___________________

This Will check if the ROW Data was just changed is in the Data
field Range and adds or removes rows.(Calls on Function CHKROW Above)

ON EVENT CHANGE(
SET CROW = Row of the cell data changed in
SET CCOL = Collumn of the cell data changed in

IF (CCOL < 2) or CCOL 6 ) Then END
CR = CHKROW(CROW)
NR = CHKROW(CROW +1)
IF (CR=OUT or (CR=BLANK AND NR=OUT)) THEN END
IF (CR=FuLL AND NR=OUT) THEN
Copy ROW CR on Sheet1
Insert it at ROW CR+1 on Sheet1
Copy ROW CR on Sheet2
Insert it at ROW CR+1 on Sheet2
(Only copy Formating and Formulas not Values)
DATALINES=DATALINES + 1
END
If (CR=BLANK AND NR=FULL) THEN
DELETE ROW CR on SHeet1
DELETE ROW CR on SHeet2
DATALINES=DATALINES - 1
END

END EVENT

An additional reference on Chip Pearson's page

http://www.cpearson.com/excel/events.htm

--
Regards,
Tom Ogilvy

"Rob van Gelder" wrote in message
...
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


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Dean Goodmen" wrote in message
...
I have done some programming, but never any VBA in excel. I am needing
some help writing 2 ON EVENTS and 1 Function listed below. The logic
works, but the commands and Syntax are what I need help with.
ANY help would be greatly appreciated :-)


_______________On OPEN EVENT_____________________
This will tell me how many lines of data are on the sheet
when it is opend. (The + 5 is to allow for my headers)
Calls to the Function below.

ON OPEN EVENT()
Set DATALINES = 0
STart Loop
Datalines = Datalines + 1
IF CHKROW(DATALINES+5) = BLANK Then END
ELSE LOOP

_______________FUNCTION__________________

This Function returns the Status of the Row sent to it
and Returns Either FULL BLANK OR OUT


FUNCTION CHKROW(RTBC (ROW TO BE CHECKED)

IF ((RTBC < DATALINES) or (RTBC DATALINES)) THEN RETURN OUT
IF COUNTBLANK(RTBC 2:RTBK 6) = 4 RETURN BLANK
Else Return FULL
END FUNCTION

_______________ON CHANGE EVENT___________________

This Will check if the ROW Data was just changed is in the Data
field Range and adds or removes rows.(Calls on Function CHKROW Above)

ON EVENT CHANGE(
SET CROW = Row of the cell data changed in
SET CCOL = Collumn of the cell data changed in

IF (CCOL < 2) or CCOL 6 ) Then END
CR = CHKROW(CROW)
NR = CHKROW(CROW +1)
IF (CR=OUT or (CR=BLANK AND NR=OUT)) THEN END
IF (CR=FuLL AND NR=OUT) THEN
Copy ROW CR on Sheet1
Insert it at ROW CR+1 on Sheet1
Copy ROW CR on Sheet2
Insert it at ROW CR+1 on Sheet2
(Only copy Formating and Formulas not Values)
DATALINES=DATALINES + 1
END
If (CR=BLANK AND NR=FULL) THEN
DELETE ROW CR on SHeet1
DELETE ROW CR on SHeet2
DATALINES=DATALINES - 1
END

END EVENT

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

I downloaded your workbook.

You put the code in the sheet code instead of the ThisWorkbook code.

To Check if it's running, insert a line like:
MsgBox "Hello World"

Or just set a breakpoint (F9). Another way to set breakpoint is from the
Debug menu, select Toggle Bookmark.

Another thing, get into the habit of using Option Explicit. It'll save you
hunting bugs.
From Tools | Options, select "Require Variable Declaration"

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Dean Goodmen" wrote in message
...

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

Seems as though i have some debuggin to do....

If starting with only one blank line, DATALINES reports 1 and ends
OPEN envent. But when you wnter data it tries to insert a line at
row3.

Also if I start with more thank 1 line of data, (1 full and one Balnk)
it goes into a endless loop. (Never returning a BLANK value from
CHKROW)

I think the problem might be in the countblank, before that line,
do I need to some how select the Sheet I wan to count blanks on?




On Wed, 10 Nov 2004 21:03:59 +1300, "Rob van Gelder"
wrote:

I downloaded your workbook.

You put the code in the sheet code instead of the ThisWorkbook code.

To Check if it's running, insert a line like:
MsgBox "Hello World"

Or just set a breakpoint (F9). Another way to set breakpoint is from the
Debug menu, select Toggle Bookmark.

Another thing, get into the habit of using Option Explicit. It'll save you
hunting bugs.
From Tools | Options, select "Require Variable Declaration"