Copy Paste Down Macro

[MCheru]

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?

 

[Per Jessen]

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

 

[MCheru]

This macro is awesome. Thank you for you're help. It works great!

 

[massi]

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

 

[Per Jessen]

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]

thanks Per,
it works.

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]

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

 

[Per Jessen]

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.

 

[massi]

hello,
it doesn't work.
i get an error message...

what am i doing wrong? when i set up the password i also tick the option for
the user to inser rows..

cheers

 

[Per Jessen]

Hello

Post your entire code, state the the error message and let me know which
line is highlighted when you click debug.

Per

 

[massi]

hello,

here it is:

Private Sub CommandButton2_Click()


'
' add_item Macro
' Macro recorded 25/03/2009 by IT Department
'
Sheets("ShutdownEvents").Unprotect Password:="qaz"
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
Sheets("ShutdownEvents").Protect Password:="qaz"
End Sub

and the error msg is:
Run time error 1004
insert method of range class failed

and it doesn't give me the option to debug but only to end

rgds

 

[Per Jessen]

Hello,
Insert a breakpoint after the line LastRow = ...., and run the macro. What
is the value of LastRow (place the mousepointer over the variable to see it)
?

Then try to qualify the sheet as you calculate LastRow:

LastRow = Sheets("ShutdownEvents").Range("A" & Rows.Count).End(xlUp).Row

Does it make any difference?

Hopes this helps.

-Per

 

[piyush mangla]

Hello Per

I just want to ask one macro for my problem.....
I want to copy all the cells of A column the number of times value in B
column.
For eg
If A1 is = 'abc' and B1 is = 12 then i would like to copy A1 '12' times
beneath it ie from A1 to A12.

Please Help to sort out