| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy Paste Down Macro
I am trying to create a macro that will search A3:G100 for blank rows. When
a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Paste Down Macro
This should do it:
Sub FillBlankRows() Dim BlankCell As Integer Dim r As Long Dim col As Long For r = 3 To 100 For col = 1 To 7 If Cells(r, col).Value = "" Then BlankCell = BlankCell + 1 End If Next If BlankCell = 7 Then Range("A" & r - 1 & ":G" & r - 1).Copy Range("A" & r) End If BlankCell = 0 Next End Sub Hopes it helps --- Per "MCheru" skrev i meddelelsen ... I am trying to create a macro that will search A3:G100 for blank rows. When a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Paste Down Macro
This macro is awesome. Thank you for you're help. It works great!
"Per Jessen" wrote: This should do it: Sub FillBlankRows() Dim BlankCell As Integer Dim r As Long Dim col As Long For r = 3 To 100 For col = 1 To 7 If Cells(r, col).Value = "" Then BlankCell = BlankCell + 1 End If Next If BlankCell = 7 Then Range("A" & r - 1 & ":G" & r - 1).Copy Range("A" & r) End If BlankCell = 0 Next End Sub Hopes it helps --- Per "MCheru" skrev i meddelelsen ... I am trying to create a macro that will search A3:G100 for blank rows. When a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Paste Down Macro
Hi there,
i have a similar request so you might able to help... i have a spread sheet where i have to record some events. the first row is already formatted and the first cell has a number 1. i would like a macro to automatically create another line each time I press a button and that would increase the number of the first cell from the previous line. i have found this one: Lastrow = Range("A" & Rows.Count).End(xlUp).Row Rows(Lastrow).Copy Rows(Lastrow).Insert Rows(Lastrow + 1).SpecialCells(xlCellTypeConstants).ClearContents but it only add the row without increasing the cell in column a hope you can help. thanks "MCheru" wrote: I am trying to create a macro that will search A3:G100 for blank rows. When a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Paste Down Macro
Hello
Try this: LastRow = Range("A" & Rows.Count).End(xlUp).Row Rows(LastRow).Copy Rows(LastRow).Insert Rows(LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents Cells(LastRow + 1, 1) = Cells(LastRow, 1) + 1 Regards, Per "massi" skrev i meddelelsen ... Hi there, i have a similar request so you might able to help... i have a spread sheet where i have to record some events. the first row is already formatted and the first cell has a number 1. i would like a macro to automatically create another line each time I press a button and that would increase the number of the first cell from the previous line. i have found this one: Lastrow = Range("A" & Rows.Count).End(xlUp).Row Rows(Lastrow).Copy Rows(Lastrow).Insert Rows(Lastrow + 1).SpecialCells(xlCellTypeConstants).ClearContents but it only add the row without increasing the cell in column a hope you can help. thanks "MCheru" wrote: I am trying to create a macro that will search A3:G100 for blank rows. When a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Paste Down Macro
thanks Per,
it works. "Per Jessen" wrote: Hello Try this: LastRow = Range("A" & Rows.Count).End(xlUp).Row Rows(LastRow).Copy Rows(LastRow).Insert Rows(LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents Cells(LastRow + 1, 1) = Cells(LastRow, 1) + 1 Regards, Per "massi" skrev i meddelelsen ... Hi there, i have a similar request so you might able to help... i have a spread sheet where i have to record some events. the first row is already formatted and the first cell has a number 1. i would like a macro to automatically create another line each time I press a button and that would increase the number of the first cell from the previous line. i have found this one: Lastrow = Range("A" & Rows.Count).End(xlUp).Row Rows(Lastrow).Copy Rows(Lastrow).Insert Rows(Lastrow + 1).SpecialCells(xlCellTypeConstants).ClearContents but it only add the row without increasing the cell in column a hope you can help. thanks "MCheru" wrote: I am trying to create a macro that will search A3:G100 for blank rows. When a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Paste Down Macro
here i am again..
there was something i haven't thought; the sheet will be protected and when running the macro an error appears (run-time error'1004') is there a way to make the macro work even if te heet is protected? thanks "massi" wrote: thanks Per, it works. "Per Jessen" wrote: Hello Try this: LastRow = Range("A" & Rows.Count).End(xlUp).Row Rows(LastRow).Copy Rows(LastRow).Insert Rows(LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents Cells(LastRow + 1, 1) = Cells(LastRow, 1) + 1 Regards, Per "massi" skrev i meddelelsen ... Hi there, i have a similar request so you might able to help... i have a spread sheet where i have to record some events. the first row is already formatted and the first cell has a number 1. i would like a macro to automatically create another line each time I press a button and that would increase the number of the first cell from the previous line. i have found this one: Lastrow = Range("A" & Rows.Count).End(xlUp).Row Rows(Lastrow).Copy Rows(Lastrow).Insert Rows(Lastrow + 1).SpecialCells(xlCellTypeConstants).ClearContents but it only add the row without increasing the cell in column a hope you can help. thanks "MCheru" wrote: I am trying to create a macro that will search A3:G100 for blank rows. When a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Copy Paste Down Macro
Hi again
You have to unprotect the sheet by macro the do your stuff and protect the sheet again: Sheets("Sheet1").UnProtect Password:="JustMe" 'Your code Sheets("Sheet1").Protect Password:="JustMe" Hopest this helps. -- Per "massi" skrev i meddelelsen ... here i am again.. there was something i haven't thought; the sheet will be protected and when running the macro an error appears (run-time error'1004') is there a way to make the macro work even if te heet is protected? thanks "massi" wrote: thanks Per, it works. "Per Jessen" wrote: Hello Try this: LastRow = Range("A" & Rows.Count).End(xlUp).Row Rows(LastRow).Copy Rows(LastRow).Insert Rows(LastRow + 1).SpecialCells(xlCellTypeConstants).ClearContents Cells(LastRow + 1, 1) = Cells(LastRow, 1) + 1 Regards, Per "massi" skrev i meddelelsen ... Hi there, i have a similar request so you might able to help... i have a spread sheet where i have to record some events. the first row is already formatted and the first cell has a number 1. i would like a macro to automatically create another line each time I press a button and that would increase the number of the first cell from the previous line. i have found this one: Lastrow = Range("A" & Rows.Count).End(xlUp).Row Rows(Lastrow).Copy Rows(Lastrow).Insert Rows(Lastrow + 1).SpecialCells(xlCellTypeConstants).ClearContents but it only add the row without increasing the cell in column a hope you can help. thanks "MCheru" wrote: I am trying to create a macro that will search A3:G100 for blank rows. When a blank row is found, I want the macro to copy the contents in the cell above it A(blank):G (blank) and paste those contents in each blank row going down ((A(blank):G (blank)) until the next row with contents is reached. Is that possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro to copy and paste values (columns)I have a macro file built | Excel Programming | |||
| copy multiple worksheets of a workbook, and paste onto a Word document ( either create new doc file or paste onto an existing file.) I need this done by VBA, Excel Macro | Excel Programming | |||
| Copy and Paste macro needs to paste to a changing cell reference | Excel Programming | |||
| how to count/sum by function/macro to get the number of record to do copy/paste in macro | Excel Programming | |||
| Macro to Copy/Paste then Paste to Next Line | Excel Programming | |||
Hybrid Mode
