Copy/Insert Rows Help needed

[Dean Goodmen]

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

 

[Doug Glancy]

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

 

[Dave Peterson]

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

 

[Dean Goodmen]

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)

 

[Doug Glancy]

Thanks, Dave.

Doug

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

 

[Dean Goodmen]

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

Thanks, Dave.

Doug

 

[Doug Glancy]

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

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

 

[Dave Peterson]

Thanks for the correction.

Honest, I meant .clearcontents, really!

Dean,

Change the Clear to ClearContents and that should do it.

hth,

Doug Glancy

 

[Dean Goodmen]

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?

 

[Doug Glancy]

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]

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?

 

[Doug Glancy]

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]

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)
--------------------------------------

 

[Doug Glancy]

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

 

[Jamie Collins]

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.

--