Auto filling cells in a column

[Guest]

I have a report that looks like this:

Part # Order # Qty

123 5678 7
5346 3
9988 10
6665 6
7524 2297 3
6685 4
8963 4444 66
3425 44

And I need it to look like this so I can sort and use the data:

Part # Order # Qty

123 5678 7
123 5346 3
123 9988 10
123 6665 6
7524 2297 3
7524 6685 4
8963 4444 66
8963 3425 44

Is there a way to fill cells with the value from the closest cell containing
data above it?

 

[George Nicholson]

Public Sub FillDownColumnA()

Dim col As Range
Dim c As Range

Set col = ActiveSheet.Range("B:B")

For Each c In col
If Len(c) = 0 Then
' Exit when 1st empty cell in Column B is encountered
Exit Sub
Else
If Len(c.Offset(0, -1)) = 0 Then
' Cell in column A is empty. Copy value from previous row.
c.Offset(0, -1) = c.Offset(-1, -1)
End If
End If
Next c

End Sub

HTH,

 

[Guest]

Check out ASAP Utilities, a free add-in available at www.asap-utilities.com

Vaya con Dios,
Chuck, CABGx3

 

[Guest]

Here's another option that doesn't involve code or add-ins:

- Insert a helper column next to your part # column. For this example, I'll
assume Part # is column A and the new helper column is column B.
- In cell B2, enter this formula: =IF(A2="",B1,A2)
- Copy the formula down the helper column for all of your data
- Now, select all the data in Column B
- Copy
- Select all the data in Column A
- "Paste Special" and check "Values"
- Delete the helper column.

HTH,
Elkar

 

[Dave Peterson]

Debra Dalgleish shares some techniques:
http://contextures.com/xlDataEntry02.html

 

[Guest]

Hi,

This section describes the following:

A. Fill formulas into adjacent cells
B. Sort a Range

A. Fill formulas into adjacent cells

1. Select the cell that contains the formula that you want to fill into
adjacent cells.
2. Drag the fill handle across the cells that you want to fill.
3. You can use the Auto Fill Options button , which appears after you drag
the fill handle, to choose how to fill the selection. For example, you can
choose Fill Formatting Only or Fill Without Formatting.

Note:

You can also fill the active cell with the contents of the cell above it.
Point to Fill on the Edit menu, and then click Down (or press CTRL+D). To
fill the active cell with the contents of the cell to the left, point to Fill
on the Edit menu, and then click Right (or press CTRL+R).

Tip You can automatically fill a formula downward, for all adjacent cells
that it applies to, by double-clicking the fill handle of the first cell that
contains the formula. For example, you have numbers in cells A1:A15 and
B1:B15, and you type the formula =A1+B1 into cell C1. To copy that formula
into cells C2:C15, select cell C1 and double-click the fill handle.

####################################################

B. Sort a Range

Sort rows by four criteria (columns)

1. Click a cell in the range you want to sort.
2. On the Data menu, click Sort.
3. In the first Sort by box click the column of least importance.
4. Click OK.
5. On the Data menu, click Sort.
6. In the Sort by and Then by boxes, click the other three columns you want
to sort, starting with the most important.
7. Select any other sort options you want, and then click OK.

Challa Prabhu

 

[Guest]

I have copied/ pasted the macro from Debra's site into a VB sheet. When the
macro is run it shows up an error symbol with 400. I have no knowledge of VB,
could any one help out.

 

[Dave Peterson]

Maybe doing the manual technique would be better for you.