If this were a one-time-shot, I don't think I'd use a macro.
Select your range in column A (A12:A38)
Then Edit|goto|Special|click Blanks
then type = (equal sign)
hit up arrow key
hit ctrl-enter.
The blank cells are filled with a formula that point at the cell above. Now
convert that range to values (edit|copy, edit|paste special|values).
Apply Data|Filter|autofilter to column B.
Filter on blanks.
select the cells in column A (within your range (a12:A38))
hit the delete key.
Now filter using "contains" "subtotal" or whatever is unique in that cell.
select the cells in column A again
and hit the delete key.
Remove the filter.
Debra Dalgleish has nicer instructions for this kind of thing at:
http://www.contextures.com/xlDataEntry02.html
But as a macro, I'd just loop through the rows:
Option Explicit
Sub testme01()
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim wks As Worksheet
Set wks = ActiveSheet
With wks
FirstRow = 12
LastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
For iRow = FirstRow + 1 To LastRow 'first row is already ok!
If IsEmpty(.Cells(iRow, "A")) Then
If IsDate(.Cells(iRow, "B").Value) Then
.Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value
End If
End If
Next iRow
End With
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
Ozbobeee wrote:
Sorry,
Some typos in the 2nd paragraph.
It should read:
Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col A (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col A have been accounted for.
Cheers
Bob
Maitland Australia
On Fri, 08 Aug 2003 17:11:08 +1000, Ozbobeee
wrote:
XL2K
The layout of data is in the following format, with the number of
entries against each item in Col A dynamic. Data will always commence
at A12. Col A entries will always be 3 alpha and Col B will always be
in date format. Each series of entries against an entry in Col A will
end with blank cells in Cols A & B, followed by a line of text in Col
B, followed by 2 blanks cells in A & B.
Taking the example, I wish to copy A12 down Col A (subject to there
being a corresponding entry in Col B), then stop when a blank cell in
Col B is found (eg B16). Then go to each entry in Col B (eg CLC, CLF
etc. etc) and invoke the same copy/paste criteria until all entries in
Col B have been accounted for.
Naturally if there is only 1 entry against data in Col A (eg Row 20),
no action is required.
A B
12 CLB 23/4/2003
13 30/4/2003
14 30/4/2003
15 2/5/2003
16
17 Sub Total Text
18
19
20 CLC 22/4/2003
25
26 SubTotal Text
27
28
29 CLF 28/4/2003
30 28/4/2003
31 29/4/2003
32
33 SubTotal Text
34
35
36 CLG
37
38
Any assistance with code to achieve the task would be greatly
appreciated.
Cheers
Bob
Maitland Australia
--
Dave Peterson