Copy/Insert Rows Help needed
 

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

Copy/Insert Rows Help needed
 

Dean,

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
End With

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...
I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

Copy/Insert Rows Help needed
 

I'd just copy it like you did (assuming that you're using a newer version of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe out the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel, you'll want to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

That fixed my cell formatting issues, but it is still copying the
values is the cells as well. (This is suppose to be adding a blank
input line to a spreadsheet)


On Sat, 13 Nov 2004 22:06:08 -0800, "Doug Glancy"
wrote:

Dean,

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
End With

hth,

Doug Glancy

"Dean Goodmen" wrote in message
.. .
I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

Copy/Insert Rows Help needed
 

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a newer version
of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe out the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel, you'll want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

Getting closer....Tehn the clear command is used it not only clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a newer version
of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe out the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel, you'll want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel, you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

Thanks for the correction.

Honest, I meant .clearcontents, really!



Doug Glancy wrote:

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel, you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson



--

Dave Peterson

Copy/Insert Rows Help needed
 

Great :-) Thanks for the help you two!

Got another one that is driving me NUTZ


This is suppose to check row B5:J5 for number of blank cells (RTBC=5)
But for some reason it is returning 7 (when it should be 8)

Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
MyValue = WorksheetFunction.CountBlank(MyRange)

Any ideas why?


On Mon, 15 Nov 2004 18:53:42 -0600, Dave Peterson
wrote:

Thanks for the correction.

Honest, I meant .clearcontents, really!



Doug Glancy wrote:

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel, you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value cRow,
and insert a copy of it just below that row. (Only the Formaulas AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

Dean,

In your code, Cells(2,RTBC) refers to E2, not B5 as you intend (Cells(row,
column) is the way it works). Also, J is the 10th column/letter of the
alphabet, not the 9th.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...
Great :-) Thanks for the help you two!

Got another one that is driving me NUTZ


This is suppose to check row B5:J5 for number of blank cells (RTBC=5)
But for some reason it is returning 7 (when it should be 8)

Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
MyValue = WorksheetFunction.CountBlank(MyRange)

Any ideas why?


On Mon, 15 Nov 2004 18:53:42 -0600, Dave Peterson
wrote:

Thanks for the correction.

Honest, I meant .clearcontents, really!



Doug Glancy wrote:

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe
out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial
Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel,
you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value
cRow,
and insert a copy of it just below that row. (Only the Formaulas
AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow +
1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

I had the it set up as Row, Columns but it was not working so I
changed it. And I had counted the columns over and over to see
if I had it wrong. I now see the problem.....I have no column H
That is correcct, it goes from G to I. I have tired delteing columns
and inserting them, but no others vanish, any clue how I can get
column H back?

On Tue, 16 Nov 2004 11:38:05 -0800, "Doug Glancy"
wrote:

Dean,

In your code, Cells(2,RTBC) refers to E2, not B5 as you intend (Cells(row,
column) is the way it works). Also, J is the 10th column/letter of the
alphabet, not the 9th.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
.. .
Great :-) Thanks for the help you two!

Got another one that is driving me NUTZ


This is suppose to check row B5:J5 for number of blank cells (RTBC=5)
But for some reason it is returning 7 (when it should be 8)

Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
MyValue = WorksheetFunction.CountBlank(MyRange)

Any ideas why?


On Mon, 15 Nov 2004 18:53:42 -0600, Dave Peterson
wrote:

Thanks for the correction.

Honest, I meant .clearcontents, really!



Doug Glancy wrote:

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then wipe
out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial
Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel,
you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value
cRow,
and insert a copy of it just below that row. (Only the Formaulas
AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow +
1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

Dean,

You must have a column H. If you can't see it, it must be hidden. Select
the entire columns G and I, right-click and choose "Unhide" to see it.

More generally, I often use the .Select method to test my assumptions about
ranges. For example, substitute your code:
Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
with:
Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC)).Select
and put a breakpoint at the next line of code. Then look at the sheet and
see if what's selected is what you expected. You can also use msgboxes, the
immediate window, whatever, to test your assumptions.

(I got my BS in geography and still often can't tell my east from west. In
Excel, it's amazing how much time I spend on mistakes caused by mixing up my
rows and columns. The above kind of testing often reveals these errors.)

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...
I had the it set up as Row, Columns but it was not working so I
changed it. And I had counted the columns over and over to see
if I had it wrong. I now see the problem.....I have no column H
That is correcct, it goes from G to I. I have tired delteing columns
and inserting them, but no others vanish, any clue how I can get
column H back?

On Tue, 16 Nov 2004 11:38:05 -0800, "Doug Glancy"
wrote:

Dean,

In your code, Cells(2,RTBC) refers to E2, not B5 as you intend
(Cells(row,
column) is the way it works). Also, J is the 10th column/letter of the
alphabet, not the 9th.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
.. .
Great :-) Thanks for the help you two!

Got another one that is driving me NUTZ


This is suppose to check row B5:J5 for number of blank cells (RTBC=5)
But for some reason it is returning 7 (when it should be 8)

Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
MyValue = WorksheetFunction.CountBlank(MyRange)

Any ideas why?


On Mon, 15 Nov 2004 18:53:42 -0600, Dave Peterson
wrote:

Thanks for the correction.

Honest, I meant .clearcontents, really!



Doug Glancy wrote:

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only
clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a
newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then
wipe
out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial
Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel,
you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value
cRow,
and insert a copy of it just below that row. (Only the
Formaulas
AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow +
1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

Yes it was hidden, I had actually figured out how to do it before
reading your responce LOL

I have been using the MSGBOX for test, but the .select will come
in handy for sure. Thanks for the tips.

I am fairly new to this stuff. I have done some Proggaming in
C++ and Pascal many years ago, as well as su\ome stuff in Mirc
scripting. It has been a bit of a chalenge to learn this stuff, but I
think I am picking stuff up pretty quicky. (You have been a great help
in that area :-) After become more comfortable in excell VB I am going
to working some in acess and SQL.

BTW I one more simple problem... How do I delcare a varible as
being global?
---------------------------------------
Private Sub Workbook_Open()
DataLines = 0
Do
DataLines = DataLines + 1
MsgBox DataLines
If ChkRow(DataLines + 4) = Blank Then Exit Do
Loop

The code above counts how many data lines I have on the sheet, it is
working just fine, but I need to access the variable DataLines in
other Subs. (Outside of this sub, it has a null value)
--------------------------------------

On Wed, 17 Nov 2004 12:28:12 -0800, "Doug Glancy"
wrote:

Dean,

You must have a column H. If you can't see it, it must be hidden. Select
the entire columns G and I, right-click and choose "Unhide" to see it.

More generally, I often use the .Select method to test my assumptions about
ranges. For example, substitute your code:
Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
with:
Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC)).Select
and put a breakpoint at the next line of code. Then look at the sheet and
see if what's selected is what you expected. You can also use msgboxes, the
immediate window, whatever, to test your assumptions.

(I got my BS in geography and still often can't tell my east from west. In
Excel, it's amazing how much time I spend on mistakes caused by mixing up my
rows and columns. The above kind of testing often reveals these errors.)

hth,

Doug Glancy

"Dean Goodmen" wrote in message
.. .
I had the it set up as Row, Columns but it was not working so I
changed it. And I had counted the columns over and over to see
if I had it wrong. I now see the problem.....I have no column H
That is correcct, it goes from G to I. I have tired delteing columns
and inserting them, but no others vanish, any clue how I can get
column H back?

On Tue, 16 Nov 2004 11:38:05 -0800, "Doug Glancy"
wrote:

Dean,

In your code, Cells(2,RTBC) refers to E2, not B5 as you intend
(Cells(row,
column) is the way it works). Also, J is the 10th column/letter of the
alphabet, not the 9th.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
.. .
Great :-) Thanks for the help you two!

Got another one that is driving me NUTZ


This is suppose to check row B5:J5 for number of blank cells (RTBC=5)
But for some reason it is returning 7 (when it should be 8)

Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
MyValue = WorksheetFunction.CountBlank(MyRange)

Any ideas why?


On Mon, 15 Nov 2004 18:53:42 -0600, Dave Peterson
wrote:

Thanks for the correction.

Honest, I meant .clearcontents, really!



Doug Glancy wrote:

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only
clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a
newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then
wipe
out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial
Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of excel,
you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow + 1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in value
cRow,
and insert a copy of it just below that row. (Only the
Formaulas
AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow +
1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

Dean,

Normally, I you'd declare a public variable at the begin of a module, above
any subroutines. It would be available to all subs and modules in the
project (unless you'd specified otherwise with Option Private Module). But
with a WorkBook_Open sub, which is in a Class module, I'm not sure.

I think you should start a new thread with this question and get the
benefits of others' wisdom.

hth,

Doug

"Dean Goodmen" wrote in message
...

Yes it was hidden, I had actually figured out how to do it before
reading your responce LOL

I have been using the MSGBOX for test, but the .select will come
in handy for sure. Thanks for the tips.

I am fairly new to this stuff. I have done some Proggaming in
C++ and Pascal many years ago, as well as su\ome stuff in Mirc
scripting. It has been a bit of a chalenge to learn this stuff, but I
think I am picking stuff up pretty quicky. (You have been a great help
in that area :-) After become more comfortable in excell VB I am going
to working some in acess and SQL.

BTW I one more simple problem... How do I delcare a varible as
being global?
---------------------------------------
Private Sub Workbook_Open()
DataLines = 0
Do
DataLines = DataLines + 1
MsgBox DataLines
If ChkRow(DataLines + 4) = Blank Then Exit Do
Loop

The code above counts how many data lines I have on the sheet, it is
working just fine, but I need to access the variable DataLines in
other Subs. (Outside of this sub, it has a null value)
--------------------------------------

On Wed, 17 Nov 2004 12:28:12 -0800, "Doug Glancy"
wrote:

Dean,

You must have a column H. If you can't see it, it must be hidden.
Select
the entire columns G and I, right-click and choose "Unhide" to see it.

More generally, I often use the .Select method to test my assumptions
about
ranges. For example, substitute your code:
Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
with:
Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC)).Select
and put a breakpoint at the next line of code. Then look at the sheet
and
see if what's selected is what you expected. You can also use msgboxes,
the
immediate window, whatever, to test your assumptions.

(I got my BS in geography and still often can't tell my east from west.
In
Excel, it's amazing how much time I spend on mistakes caused by mixing up
my
rows and columns. The above kind of testing often reveals these errors.)

hth,

Doug Glancy

"Dean Goodmen" wrote in message
.. .
I had the it set up as Row, Columns but it was not working so I
changed it. And I had counted the columns over and over to see
if I had it wrong. I now see the problem.....I have no column H
That is correcct, it goes from G to I. I have tired delteing columns
and inserting them, but no others vanish, any clue how I can get
column H back?

On Tue, 16 Nov 2004 11:38:05 -0800, "Doug Glancy"
wrote:

Dean,

In your code, Cells(2,RTBC) refers to E2, not B5 as you intend
(Cells(row,
column) is the way it works). Also, J is the 10th column/letter of
the
alphabet, not the 9th.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
.. .
Great :-) Thanks for the help you two!

Got another one that is driving me NUTZ


This is suppose to check row B5:J5 for number of blank cells
(RTBC=5)
But for some reason it is returning 7 (when it should be 8)

Set MyRange = Worksheets(1).Range(Cells(2, RTBC), Cells(9, RTBC))
MyValue = WorksheetFunction.CountBlank(MyRange)

Any ideas why?


On Mon, 15 Nov 2004 18:53:42 -0600, Dave Peterson
wrote:

Thanks for the correction.

Honest, I meant .clearcontents, really!



Doug Glancy wrote:

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

"Dean Goodmen" wrote in message
...


Getting closer....Tehn the clear command is used it not only
clears
the values in the cell, but also clears the formatting :-(


On Sun, 14 Nov 2004 09:05:37 -0800, "Doug Glancy"
wrote:

Thanks, Dave.

Doug

"Dave Peterson" wrote in message
...
I'd just copy it like you did (assuming that you're using a
newer
version
of
excel that supports xlPasteFormulasAndNumberFormats) and then
wipe
out
the
constants that were pasted.

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial
Paste:=xlPasteFormulasAndNumberFormats
On Error Resume Next
.Rows(cRow +
1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

If you share with users who still run older versions of
excel,
you'll
want
to
combine Doug's code:

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow + 1).PasteSpecial Paste:=xlFormulas
.Rows(cRow + 1).PasteSpecial Paste:=xlFormats
On Error Resume Next
.Rows(cRow +
1).Cells.SpecialCells(xlCellTypeConstants).Clear
On Error GoTo 0
End With

Dean Goodmen wrote:

I have having trouble with the following lines....

Here is what I want it to do : Copy the Row contained in
value
cRow,
and insert a copy of it just below that row. (Only the
Formaulas
AND
Borders, not the vlaues of the cells.)

With Worksheets(1)
.Rows(cRow + 1).Insert xlShiftDown
.Rows(cRow).Copy
.Rows(cRow +
1).PasteSpeciaxlPasteFormulasAndNumberFormats
End With

--

Dave Peterson

Copy/Insert Rows Help needed
 

"Doug Glancy" wrote ...
[i]
How do I delcare a varible as
being global?

Normally, declare a public variable at the begin of a module, above
any subroutines.

The same as for any module, my advice would be to declare a Private
(module-level) variable and make it available externally using a
Property Get (optionally a Property Let/Set) of scope Friend or Public
as appropriate e.g.

Private m_strName As String

Friend Property Get Name() As String
Name = m_strName
End Property

Jamie.

--