counter

[cpra3617]

I would like a macro that, each time it is called, cuts the subsequent row (beginning at row 2) and inserts it into row 1.

So far I have:

Public zCount As Integer

Sub insertRow()
zCount = zCount + 1
Rows(zCount).Select
    Selection.Cut
    Rows("1:1").Select
    ActiveSheet.Paste
End Sub

I would like the counter to be reset the first time it encounters a blank row. So, if zCount =6 and row 6 is blank, I would like the zCount to be set to 1 again.
Can someone help with this?

 

[Jonmo1]

maybe

If worksheetfunction.countblank(range(zcount & ":" & zcount)) = 256 then zcount = 1

 

[cpra3617]

My first post was not accurate. What I really need this sub to do is to select only cells A thru I of the row in question and insert it into row A. So each time the sub was called it would insert the next row down into row A until it encountered a blank cell at which point the counter, zCount, would be set back to zero. 1st time it is called it would insert A thru I of row 2 above row 1, second time row 3 above row 1 etc.

Can someone help with the modification of the below code:

<code>
Public zCount As Integer

Sub insertRow()
zCount = zCount + 1
Rows(zCount).Select
Selection.Cut
Rows("1:1").Select
Selection.Insert Shift:=xlDown
End Sub
</code>

 

[cart0250]

Not sure how to make it not select the entire row, but to make it start over maybe something like:

<code>
Public zCount As Long

Sub insertRow()
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

If zCount <> lastRow Then
zCount = zCount + 1
Rows(zCount).Select
Selection.Cut
Rows("1:1").Select
Selection.Insert Shift:=xlDown

Else
zCount = 1
End If
End Sub
</code>

 

[cpra3617]

Thanks.

This line
<code>
Selection.Insert Shift:=xlDown
</code>

gives an error saying the paste areas may be different. It works when I do it manually, but w/in the macro it fails. Why??

 

[cart0250]

yes, I get the same error now that I try it. It may be because "zCount" does not span an equivalent range as "1:1"??

 

[cart0250]

There is Absolutely a better way, but try:
<code>
Public zCount As Variant

Sub insertRow()
Dim lastRow As Variant
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

Rows("1:1").Select
Selection.Insert Shift:=xlDown

If zCount <> lastRow Then
zCount = zCount + 1

Rows(zCount).Cut Destination:=Range("A1")
Rows(zCount).Delete

Else
zCount = 1
End If
End Sub
</code>

 

[cpra3617]

Rows(zCount).Cut Destination:=Range("A1")

Isn't there a way to modify the above line to paste zCount into the row 1 and at the same time insert shift down so I don't overwrite what is currently in row 1?

Then I wouldn't have to use:
Rows("1:1").Insert Shift:=xlDown