Macro to number blank cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Fernando Gomez - 30 Apr 2004 16:22 GMT

In one row I have:
A B C D E
F G
Cust# Address 1 Address 2 Address3 Address 4 Address5
Address6

I have a macro that inserts 6 rows below each name, how could I copy
information in columns B-G to the 6 cells below cust #, Thanks in advance
for your help

Fernando

Reply to this Message

Anne Troy - 30 Apr 2004 16:30 GMT

Can it be this simple? I'm not positive that I understand what you're
asking. But try this.
You have blank rows below each record.
Select the columns B through G, beginning with the first BLANK row and down
all rows.
Hit Edit-Go to-Special and choose Blanks, Ok.
Now, assuming your first blank row was row 3, we'll type into B3:

=b2

Then hit Ctrl+Enter.
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->
Hope this helps!
Anne Troy (better known as Dreamboat)
Author: Dreamboat on Word
Email: Com.Piersontech@Dreamboat
(Reverse it!)
Web: www.TheOfficeExperts.com
<-*-><-*-><-*-><-*-><-*-><-*-><-*-><-*->

> In one row I have:
> A B C D E
[quoted text clipped - 7 lines]
>
> Fernando

Reply to this Message

Fernando Gomez - 30 Apr 2004 16:56 GMT

When you select columns B to G then Go To==>Scpecial==>Blanks,OK
you can not write anything, I think or may be I don get your idea
What I try to do is get a Macro that will copy B2 to A3, C2 to A4, D2 to A5,
E2 to A6, F2 to A7; then you find another name below 6 more blank spaces.

Thanks

> Can it be this simple? I'm not positive that I understand what you're
> asking. But try this.
[quoted text clipped - 28 lines]
> >
> > Fernando

Reply to this Message

Dave Peterson - 30 Apr 2004 23:57 GMT

I think Anne thought that you were filling blank cells under B:G--not doing a
copy|paste special|Transpose (essentially).

Here's one that inserts the 6 rows and does the copy:

Option Explicit
Sub testme()

Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet

Set wks = Worksheets("sheet1")

With wks
FirstRow = 1 'no headers???
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row

For iRow = LastRow To FirstRow Step -1
.Cells(iRow, "A").Offset(1, 0).Resize(6, 1).EntireRow.Insert
.Cells(iRow, "B").Resize(1, 6).Copy
.Cells(iRow + 1, "A").PasteSpecial Transpose:=True
Next iRow

End With

End Sub

> When you select columns B to G then Go To==>Scpecial==>Blanks,OK
> you can not write anything, I think or may be I don get your idea
[quoted text clipped - 37 lines]
> > >
> > > Fernando

Signature

Dave Peterson
ec35720@msn.com

Reply to this Message