Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I auto fill an entire spreadsheet at once?
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? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I auto fill an entire spreadsheet at once?
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I auto fill an entire spreadsheet at once?
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) "Carol F." wrote: 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? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I auto fill an entire spreadsheet at once?
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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I auto fill an entire spreadsheet at once?
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 "Carol F." <Carol wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto fill for data from another worksheet | Excel Discussion (Misc queries) | |||
Trunc entire spreadsheet????? | Excel Discussion (Misc queries) | |||
Does Excel support Auto fill on filtered data? | Excel Discussion (Misc queries) | |||
Convert my entire spreadsheet from inches to centimeters | Excel Discussion (Misc queries) | |||
How do I auto fill blanks cells immediately below with the same i. | Excel Discussion (Misc queries) |