Defining ranges in Excel 2007 VBA

[phudgens]

I am using the following code to define a range in Excel 2007 VBA:

With ThisWorkbook.Worksheets(MainWks)
    Set List = .Range("F3", .Range("F5000").End(xlUp))
End With

This works fine if there are any non-empty cells in the range from F3 to F5000. If there isn't, List will become the first non-empty cell found above cell F3. I can't figure out a way to define a range that is confined to non-empty cells between F3 and F5000. Once I've defined that range, I want to find the number of non-empty cells. Can anyone provide some insight?

Thanks,
Paul Hudgens

 

[SkipVought]

hi,

What's special about row 5000?

Yes, the End property "looks" for the first cell in the given direction with a VALUE, so if column F is empty, TILT!!!

Describe the structure of your data. You might be able to use either CurrentRegion or UsedRange. Each has their features and limitations.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Gavona]

Once you have worked out how to define the range

   ans = MsgBox(" There are " & Format(Range("A3:A38").Cells.Count - Range("A3:A38").SpecialCells(xlCellTypeBlanks).Cells.Count, "##0") & "  non-blank cells", vbOKOnly)

If that doesn't give the expected answer then some cells might not be properly blank. You might want to look at the other SpecialCells - perhaps you can count what you need to count directly.

In xl2003 use Edit, Goto, Special

Gavin

 

[phudgens]

Thanks guys for the responses. Column F is where the user enters items to search for in a file defined later in the program. If for whatever reason the user has cleared column F, I need to display a message that no items were found. 5000 is an arbitrary number of items that I know will not be exceeded. Gavin's Cells.Count method works great for what I need. One question: how can a cell appear empty, but in reality not be?

Thanks again,
Paul Hudgens

 

[SkipVought]

how can a cell appear empty, but in reality not be?

I know users who have a REALLY BAD hadit of "deleting" data in a cell by hitting the SPACE BAR!!!!

LOOKS empty, but its NOT!

Also, a cell might be EMPTY, but still have DATA: certain formatting properties, for instance.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[phudgens]

Good to know - thanks very much.