Find Second Blank Row

[Guest]

Hi, help would be appreciated on this please:

I currently have a macro that will copy the contents of a couple of rows on
another worksheet when a button is pressed. However, the macro copies the
data on the same row as which it was originally recorded. What I want it to
do is to work down from the top and copy the required rows once it finds 2
blank rows (in a row so to speak). So that in effect there is a blank row
between each group of rows with contents in them. I presume I need to add a
line if code to tell it to do that.

I hope that makes sense.

Thanks for advance.

 

[Bernie Deitrick]

Nejl,

Generally, it is always better to post your code. And almost always, it is
better to work up from the bottom when trying to find blanks at the bottom:
your extra blank line would require multiple end-down key combinations, with
the number dependent on the actual number of times you've run your macro.

For example, the second blank row at the bottom of column A is:

Dim myRow As Long
myRow = Range("A65536").End(xlUp)(3).Row
MsgBox "The second blank row is " & myRow

Even then, this will be wrong when column A is completely blank - it will
return row 3 and not row 2.

HTH,
Bernie
MS Excel MVP

 

[Bernie Deitrick]

Nejl,

If you are truly pasting at the bottom, there is no need for an insert.

And in Excel, you hardly ever need to select anything to have the macro
work.

Below is a modified macro that should do what you want, unless you actually
need to insert. Post back if that is the case, and I will modify the code.

HTH,
Bernie
MS Excel MVP

Sub NEWRISK2()
Sheets("Template").Rows("1:3").Copy _
Sheets("Risk Register Example").Range("A65536").End(xlUp)(3).EntireRow
End Sub

 

[Guest]

Thanks for your help it now works fine. I used an insert so that it pushes a
form button downwards, ensuring the button is always at the bottom of the
sheet.

The code i used in the end was:

Sub NEWRISK()
'
' NEWRISK Macro
' Macro recorded 01/09/2004 by nathanl
'

'
Sheets("Template").Select
Rows("1:3").Select
Range("B1").Activate
Selection.Copy
Sheets("Risk Register").Select
Selection.Insert Shift:=Range("A65536").End(xlUp)(3).Select
Range("B65536").End(xlUp)(3).Select
End Sub

Many Thanks

Nejl