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?

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" <(E-Mail Removed)> skrev i meddelelsen
news:FD48F2EE-2BDC-422A-AB13-(E-Mail Removed)...
>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?

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" <(E-Mail Removed)> skrev i meddelelsen
> news:FD48F2EE-2BDC-422A-AB13-(E-Mail Removed)...
> >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?
>
>

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?

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" <(E-Mail Removed)> skrev i meddelelsen
news:55824FCE-AE02-40FC-BE5C-(E-Mail Removed)...
> 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?

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" <(E-Mail Removed)> skrev i meddelelsen
> news:55824FCE-AE02-40FC-BE5C-(E-Mail Removed)...
> > 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?
>
>

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" <(E-Mail Removed)> skrev i meddelelsen
> > news:55824FCE-AE02-40FC-BE5C-(E-Mail Removed)...
> > > 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?
> >
> >

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" <(E-Mail Removed)> skrev i meddelelsen
news:BD53D869-5503-4E81-83BB-(E-Mail Removed)...
> 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" <(E-Mail Removed)> skrev i meddelelsen
>> > news:55824FCE-AE02-40FC-BE5C-(E-Mail Removed)...
>> > > 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?
>> >
>> >

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" wrote:

> 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" <(E-Mail Removed)> skrev i meddelelsen
> news:BD53D869-5503-4E81-83BB-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> skrev i meddelelsen
> >> > news:55824FCE-AE02-40FC-BE5C-(E-Mail Removed)...
> >> > > 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?
> >> >
> >> >
>
>

Hello

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

Per

"massi" <(E-Mail Removed)> skrev i meddelelsen
news:40518E59-24B0-45A3-9A76-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> skrev i meddelelsen
>> news:BD53D869-5503-4E81-83BB-(E-Mail Removed)...
>> > 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" <(E-Mail Removed)> skrev i meddelelsen
>> >> > news:55824FCE-AE02-40FC-BE5C-(E-Mail Removed)...
>> >> > > 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?
>> >> >
>> >> >
>>
>>