making list, selecting empty cells
 

Dear Fellows,
I have a lot of my stamps listed in MS Excel and want to do some fairly
advanced data base manipulations on them such as sorting on specific
criteria, generating want lists, general organization etc. I know there are
lots of stamp data
base programs out there, but since I already have a few thousand entered
with descriptions, page # and CV (and lots of other stuff) it would be way
to much effort to transport them to another program. What I'm looking for
is someone who is familiar with logical operators in macros. I have pretty
much all the function knowledge I need, but the logical macro manipulation
needs help.

Here is what I want to do first (extremely simplified).... Say in an Excel
sheet you have 2 columns of values (the column to the left is the cat #, and
the column on the right is something like a U or a M, or it could be a
dollar value, what ever). I want to go down the left column and check each
cell to the right of that cell to see if it is occupied by a value,
number..what ever. If the cell to the right is blank (like ISNUM, false)
then take the left cell's text/value and paste it to another location which
constitutes a list. As the check continues down the left column, the list
grows. Of course this is a dynamic list as every time you run the macro the
list potentially changes. I know this can be done, I have seen it. Can
you help me???
Thanks
Wolf-==-

making list, selecting empty cells
 

Hi,
See if this works for you.
Sub Macro1()
'Start on the title of the Cat #, will work its way down and
'transfer the Cat# to "NewList" sheet, which will be created,
'where there is not value to the left of the Cat #
ThisSheet = ActiveSheet.Name
Sheets(ThisSheet).Select
TitleValue = ActiveCell.Value
Sheets.Add
NewSheet = ActiveSheet.Name
Sheets(NewSheet).Select
Sheets(NewSheet).Name = "NewList"
Range("A1").Select
ActiveCell.Value = TitleValue
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Do Until ActiveCell.Value = ""
If ActiveCell.Offset(0, 1).Value = "" Then
TransferValue = ActiveCell.Value
Sheets("NewList").Select
ActiveCell.Value = TransferValue
ActiveCell.Offset(1, 0).Range("A1").Select
Sheets(ThisSheet).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Else
ActiveCell.Offset(1, 0).Range("A1").Select
End If
Loop

End Sub

Thanks,

"wolfb" wrote:

Dear Fellows,
I have a lot of my stamps listed in MS Excel and want to do some fairly
advanced data base manipulations on them such as sorting on specific
criteria, generating want lists, general organization etc. I know there are
lots of stamp data
base programs out there, but since I already have a few thousand entered
with descriptions, page # and CV (and lots of other stuff) it would be way
to much effort to transport them to another program. What I'm looking for
is someone who is familiar with logical operators in macros. I have pretty
much all the function knowledge I need, but the logical macro manipulation
needs help.

Here is what I want to do first (extremely simplified).... Say in an Excel
sheet you have 2 columns of values (the column to the left is the cat #, and
the column on the right is something like a U or a M, or it could be a
dollar value, what ever). I want to go down the left column and check each
cell to the right of that cell to see if it is occupied by a value,
number..what ever. If the cell to the right is blank (like ISNUM, false)
then take the left cell's text/value and paste it to another location which
constitutes a list. As the check continues down the left column, the list
grows. Of course this is a dynamic list as every time you run the macro the
list potentially changes. I know this can be done, I have seen it. Can
you help me???
Thanks
Wolf-==-

making list, selecting empty cells
 

Hi Wolf,

Try this ...

Sub FilterBlanksInList()
Dim cl As Range
Dim iFirstTime As Boolean

' Assuming that the worksheet containing your list is the currently
active worksheet
' and that the column that you want output is the first column in the
list - nothing else to the left.

iFirstTime = True
' Clear any old results in your chosen worksheet output range from
previous runs.
' Recommendation: Don't put anything else on this output sheet as it
will be cleared/deleted here.
Worksheets(3).UsedRange.EntireColumn.Delete

' Identify the range to test and loop through it.
' (Also assumes that there is a heading row and skips it).
For Each cl In
ActiveSheet.UsedRange.Resize(ActiveSheet.UsedRange .Rows.Count - 1,
1).Offset(1, 0)
' Test the cell in the next column to the right
If IsEmpty(cl.Offset(0, 1).Value) Then
If iFirstTime Then
' Output Heading and subsequent results to the worksheet
range chosen above.
' (The cell in row 1, column 1 on the third worksheet just
' for the sake of this example - feel free to amend as
desired).
With Worksheets(3).Cells(1, 1)
.Value = ActiveSheet.UsedRange.Cells(1, 1).Value
.Font.Bold = True
End With
iFirstTime = False
End If
cl.Copy

Worksheets(3).UsedRange.Offset(Worksheets(3).UsedR ange.Rows.Count,
0).Resize(1, 1).PasteSpecial
End If
Next cl
Application.CutCopyMode = False
End Sub

----------

HTH, Sean.

"wolfb" wrote:

Dear Fellows,
I have a lot of my stamps listed in MS Excel and want to do some fairly
advanced data base manipulations on them such as sorting on specific
criteria, generating want lists, general organization etc. I know there are
lots of stamp data
base programs out there, but since I already have a few thousand entered
with descriptions, page # and CV (and lots of other stuff) it would be way
to much effort to transport them to another program. What I'm looking for
is someone who is familiar with logical operators in macros. I have pretty
much all the function knowledge I need, but the logical macro manipulation
needs help.

Here is what I want to do first (extremely simplified).... Say in an Excel
sheet you have 2 columns of values (the column to the left is the cat #, and
the column on the right is something like a U or a M, or it could be a
dollar value, what ever). I want to go down the left column and check each
cell to the right of that cell to see if it is occupied by a value,
number..what ever. If the cell to the right is blank (like ISNUM, false)
then take the left cell's text/value and paste it to another location which
constitutes a list. As the check continues down the left column, the list
grows. Of course this is a dynamic list as every time you run the macro the
list potentially changes. I know this can be done, I have seen it. Can
you help me???
Thanks
Wolf-==-