How do I auto fill an entire spreadsheet at once?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carol F. - 15 May 2006 02:07 GMT

I have a spreadsheet with a customer number on line 1, with additional data
about this customer on lines 2-6 (customer column on these lines is blank).
I then have a new customer number on line 7, with additional data about this
second customer on lines 8-12 (custom column on these lines is blank). This
spreadsheet has thousands of such entries.

I am hoping to find a command that will allow me to fill in the custom
number from line 1 down until I find the next custom number already filled;
then take the new customer number and fill it down until I find the next
customer number, etc. Do you know of a command that will help me?

Reply to this Message

SK - 15 May 2006 03:22 GMT

Try following code
Dim rownum as integer
Dim custno as string
rownum=2
custno = sheet1.cells(1,1).value
while sheet1.cells(rownum-1,1) <> empty
if sheet1.cells(rownum,1).value = empty and
sheet1.cells(rownum-1,1).value <> empty Then
sheet1.cells(rownum-1,1).value=custno
else
custno = sheet1.cells(rownum-1,1).value
endif
rownum= rownum+1
wend
I think it will work
Suku

Reply to this Message

Rich Mcc - 15 May 2006 03:43 GMT

or a non macro option is to use the if function, but this will depend on how
your data is set up i use this option to direct data on some of my forms but
without seeing how yours is set up its hard to tell the best option:-

maybe insert a extra col and use

assuming customer col = col B and new col = a : in row 2

=if(b2="",a1,b2)

> I have a spreadsheet with a customer number on line 1, with additional data
> about this customer on lines 2-6 (customer column on these lines is blank).
[quoted text clipped - 6 lines]
> then take the new customer number and fill it down until I find the next
> customer number, etc. Do you know of a command that will help me?

Reply to this Message

Alan - 15 May 2006 03:54 GMT

Try this:

Signature

The views expressed are my own, and not those of my employer or anyone
else associated with me.

My current valid email address is:

1bupdvc02@sneakemail.com

This is valid as is. It is not munged, or altered at all.

It will be valid for AT LEAST one month from the date of this post.

If you are trying to contact me after that time,
it MAY still be valid, but may also have been
deactivated due to spam. If so, and you want
to contact me by email, try searching for a
more recent post by me to find my current
email address.

The following is a (probably!) totally unique
and meaningless string of characters that you
can use to find posts by me in a search engine:

ewygchvboocno43vb674b6nq46tvb

>I have a spreadsheet with a customer number on line 1, with
>additional data
[quoted text clipped - 13 lines]
> next
> customer number, etc. Do you know of a command that will help me?

If I have understood correctly, try this:

0) Backup your workbook(s) nd read all the steps below before
beginning.

1) Autofilter the entire range / sheet

2) Filter 'Customer Column' to show only blank cells (I'll assume this
is column A)

3) Enter a formula in the first cell now showing to refer to the cell
above. For example if you have a customer number in A1 then A7, you
will be seeing A2:A6,A8:A13 etc. In A2 enter "=A1" (without the
quotes).

4) Copy this and paste down the entire range, but make sure that you
choose the option to 'select visible cells only'. This can be put on
a toolbar from the 'EDIT' list if you like: Tools - Customise -
Commands - Edit - Scroll to bottomn of list - Drag 'Select Visible
Cells' to any tool bar you have visible.

That should do it.

HTH,

Alan.

Signature

Reply to this Message

Alan - 15 May 2006 04:50 GMT

Sorry - This whole answer is in there, but got top posted (partially)
and bottom posted in full.

This is the whole answer again:

If I have understood correctly, try this:

0) Backup your workbook(s) nd read all the steps below before
beginning.

1) Autofilter the entire range / sheet

2) Filter 'Customer Column' to show only blank cells (I'll assume this
is column A)

3) Enter a formula in the first cell now showing to refer to the cell
above. For example if you have a customer number in A1 then A7, you
will be seeing A2:A6,A8:A13 etc. In A2 enter "=A1" (without the
quotes).

4) Copy this and paste down the entire range, but make sure that you
choose the option to 'select visible cells only'. This can be put on
a toolbar from the 'EDIT' list if you like: Tools - Customise -
Commands - Edit - Scroll to bottomn of list - Drag 'Select Visible
Cells' to any tool bar you have visible.

That should do it.

HTH,

Alan.

Reply to this Message

Otto Moehrbach - 15 May 2006 12:05 GMT

Carol
This macro should do what you want. I assumed your customer numbers are
in Column A starting in A1. HTH Otto
Sub FillNum()
Dim c As Long
Dim First As Range
Dim Last As Range
'It is assumed that the first customer name is in A1
Set First = [A1]
Do
If IsEmpty(First.Offset(1)) Then
Set Last = First.End(xlDown).Offset(-1)
Else
Set First = Last
End If
First.Copy Range(First, Last)
Set First = Last.Offset(1)
Loop Until First.End(xlDown).Row = Rows.Count
Set Last = Range("B" & Rows.Count).End(xlUp).Offset(, -1)
First.Copy Range(First, Last)
End Sub

>I have a spreadsheet with a customer number on line 1, with additional data
> about this customer on lines 2-6 (customer column on these lines is
[quoted text clipped - 10 lines]
> then take the new customer number and fill it down until I find the next
> customer number, etc. Do you know of a command that will help me?

Reply to this Message