Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Insert rows and copy forumla above it | Excel Discussion (Misc queries) | |||
Copy/Insert rows | Excel Discussion (Misc queries) | |||
Copy/Insert rows with formulas | Excel Worksheet Functions | |||
Copy Rows and insert these rows before a page break | Excel Programming | |||
Copy insert rows | Excel Programming |