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
Ingeborg Hawighorst 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone 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

Suggested Solutions

Title # Comments Views Activity
need 2017 datas one by one using macro 12 38
Tricky Shapes formula part 4 4 14
Overwriting Named Ranges 13 32
Excel Pivot Table question 5 16
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

726 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