Excel VBA - Testing a cell for any type of content

Posted on 2013-12-08
Last Modified: 2013-12-09
I would like to test my currently active cell for any type of content and if it has content (words, numbers, punctuation, etc.) I'd like to generate a message box saying "content"
I would then like to go to the bottom row of that region.  (If I were doing this with the keyboard, I would press the END key and then the DOWN key  This would take me to the bottom cell of my current region.
Question by:brothertruffle880
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 166 total points
ID: 39705123

something along these lines, maybe?

Sub test()
Dim rng As Range
Set rng = ActiveCell

    If Len(rng) > 0 Then
        MsgBox rng.Value
    End If

End Sub

Open in new window

You can use a different logic to populate the "rng" variable, of course.

cheers, teylyn

Author Comment

ID: 39705138
Nice.  Thanks.
Would this work to test for any value inside of a cell? (i.e. alphanumeric or punctuation?

if istext(activecell.value) then msgbox "you have junk to clean up!"
end if
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 167 total points
ID: 39705146
The correct syntax for that would be to include "Application", as in:

If Application.IsText(ActiveCell.Value) ...

Open in new window

You could also check for an empty value with:

ActiveCell.Value <> ""

Open in new window

LVL 81

Accepted Solution

byundt earned 167 total points
ID: 39705183
If the next cell is blank, you will get an unexpected result with .End(xlDown)--it skips the blank cells and goes to the next cell with data. If none exist, it goes to the last row in the worksheet. I added a test for that possibility to teylyn's code:
Sub test()
Dim rng As Range
Set rng = ActiveCell

If Len(rng) > 0 Then
    MsgBox rng.Value
    If Not IsEmpty(rng.Offset(1, 0)) Then rng.End(xlDown).Select
End If

End Sub

Open in new window


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question