Finding and compiling list of cells containing data...

[Guest]

I have ten sets of cells of ten cells each (each cell is on a different
line). Each cell may or may not contain data. I want to build a summary
sheet listing only data within the cells and the line number that that cell
is on. (omit all blank cells) I hope this makes sense.

The application is a budget worksheet that has ten categories with ten line
items in each category. Each category may contain blank lines. I want a
concise summary on a separate sheet, eliminating the category headings and
all blank lines. How do I do this?

Thanks in advance.

 

[Otto Moehrbach]

Are all these cells in one column?
When you say "eliminating the category headings", I take it that the
category headings are text and not numbers. Is that right? If that is
right, what are the contents of all the other cells that are not blank?
Numbers only? Text only? Some of each? If those cells can have text then
you need to furnish the exact text of all the categories so they can be
differentiated from the other text cells. HTH Otto

 

[Guest]

Thanks for your response, Otto.

Here is a sampling of some of the cells in question:

Transportation
c Gas
c Oil Change
c Repairs
c Tires
c Car Insurance
c License and Taxes
m Car Replacement
c Tolltag



Clothing
c Jen's Clothing
c Richard's Clothing
c Cleaning/Laundry








Medical/Health
c Doctor Bills
c Dentist
c Optometrist
c Drugs
c Contacts
s 24hr Fitness Membership
c Allergy Injections




There are headings: Transportation, Clothing, Medical/Health, etc. Below
these headings, and to the right, are the cells that I would like to look at.
All cells contain text, not numbers, and there are ten cells vertically for
every category. What I want to do is extract only the cells from this column
that actually contain text and list them in a continuous column with the
corresponding line numbers like this:

66 Gas
67 Oil Change
68 Repairs
69 Tires
70 Car Insurance
71 License and Taxes
72 Car Replacement
73 Tolltag
78 Jen's Clothing
79 Richard's Clothing
80 Cleaning/Laundry
90 Doctor Bills
91 Dentist
92 Optometrist
93 Drugs
94 Contacts
95 24hr Fitness Membership
96 Allergy Injections

Is this possible?

Thanks again.

 

[Otto Moehrbach]

Richard
This little macro does what you want. Note that this macro works on
Column B only. You had said that you didn't want the categories in Column A
picked up at all.
This macro loops through all the cells in Column B from B1 to the last entry
in the column. All blank cells are ignored.
For each occupied cell in Column B, this macro will put the row number
in Column A of a sheet named "List", and the contents of the cell in Column
B of the "List" sheet. This macro should be placed in a standard module.
Please post back if you need more or you want to make some changes. HTH
Otto
Sub ListData()
Dim RngColB As Range
Dim i As Range
Dim Dest As Range
Set Dest = Sheets("List").Range("A1")
Set RngColB = Range("B1", Range("B" & Rows.Count).End(xlUp))
For Each i In RngColB
If IsEmpty(i) Then GoTo NextCell
Dest.Value = i.Row
Dest.Offset(, 1).Value = i.Value
Set Dest = Dest.Offset(1)
NextCell:
Next i
End Sub

 

[Guest]

Thanks Otto, this strips and arranges the data quite well.

I was curious if it would be possible to tweak the way this works a bit. I
tried to do some mods to it, but was unsuccessful. I am not very familiar
with VB.

The application that I am using this in is a budget which contains a
worksheet for every month. Each sheet is labeled in the following format:
"Jan, Feb, Mar, etc.". With this labelling scheme, I use
TEXT(MONTH(NOW()),"mmm") in my formulas to access the current sheet for my
summary sheet (labeled "Summary"). It is for this summary sheet that I want
the compiled list of names and line numbers for the current month. (All
months use the same sheet format.)

Is it possible to write the macro in such a way that when I access the sheet
"Summary", it automatically runs the macro, updating the summary list?
(Rather than having to click a button or go to Tools>Macro)

Also, is it possible to format the list so that it displays in more than one
column depending on the number of entries? So if I have thirty or fewer
entries, it would just fill one column, but if it gets to be more than thirty
it would form a second column like this:

6 Entry 1 46 Entry 31
7 Entry 2 51 Entry 32
...
44 Entry 29 88
45 Entry 30 91

This would be a nice-to-have, but not an absolute necessity.

Thanks again for all your help. I really appreciate it!

 

[Otto Moehrbach]

Richard
Yes, all that can be done. A few questions though. Will the Summary
sheet always be cleared (empty)? I ask this because you say you want this
to happen whenever the Summary sheet is selected. Do you want the code to
clear the sheet (less headers) before copying the data?
Another question. You say you want to copy into multiple columns when
the list is yea long. But the list is two columns (the row number and the
data) wide already. I just want to be sure I'm not missing something in
what you say.
What you want with the multiple columns is called "snaking" the columns,
usually done prior to printing. I would write the code to copy everything
into Columns A & B initially. Once that is done the code will look at
what's there and snake it if necessary. Post back with clarification. Otto

 

[Otto Moehrbach]

Richard

The 3 macros below do what you want. The first macro is a sheet
macro and must be placed in the sheet module for the "Summary" sheet. When
this macro fires, it will call the other 2 macros. Note that this macro
will fire every time you select the "Summary" sheet. Every time. To access
the "Summary" sheet module, right-click on the "Summary" sheet tab, select
View Code. Paste the first macro into that module. You may find it
somewhat of a nuisance for this macro to fire (and set off the other two)
every time you select the Summary sheet while you are setting up your file.
To prevent this macro from firing, access the Summary sheet module and
remark out all 3 lines of code.

The other two macros go in a standard module.

When you select the "Summary" sheet, the following will take place:

The used range of the "Summary" sheet will be cleared.

The code will figure out which sheet is for the current month (you must have
12 sheets named Jan, Feb, Mar, etc).

The data in this month's sheet will be copied to the "Summary" sheet as we
said before.

If Columns A:B of the "Summary" sheet (used range) exceeds row 30, the code
will snake the data into neighboring columns.

Note that the code will not insert a blank column between the snaked
columns. I didn't know if you wanted that or not. Come back if you want
that.

If you are unsure of where to put what macros, email me and I'll
send you a small file that has everything placed properly. My email address
is (e-mail address removed). Remove the "nop" from this address. HTH Otto





Private Sub Worksheet_Activate()

Call GetSummary

End Sub



Sub GetSummary()

Dim RngColB As Range

Dim i As Range

Dim Dest As Range

Application.ScreenUpdating = False

ActiveSheet.UsedRange.ClearContents

Set Dest = Range("A1")

With Sheets(Format(Date, "mmm"))

Set RngColB = .Range("B1", .Range("B" & Rows.Count).End(xlUp))

For Each i In RngColB

If IsEmpty(i) Then GoTo NextCell

Dest.Value = i.Row

Dest.Offset(, 1).Value = i.Value

Set Dest = Dest.Offset(1)

NextCell:

Next i

End With

Call SnakeSum

Application.ScreenUpdating = True

MsgBox "Summary is complete."

End Sub



Sub SnakeSum()

Dim HowMany As Long

Dim RngCopy As Range

Dim Dest As Range

HowMany = 30

If Range("A" & Rows.Count).End(xlUp).Row <= HowMany Then Exit Sub

Set Dest = Range("C1")

Set RngCopy = Cells(1, 1)

Do

RngCopy.Resize(HowMany, 2).Copy Dest

Set RngCopy = RngCopy.Offset(HowMany)

Set Dest = Dest.Offset(, 2)

Loop Until IsEmpty(RngCopy.Value)

Columns("A:B").Delete

End Sub