• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 422
  • Last Modified:

Excel VBA - Testing a cell for any type of content

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.
0
brothertruffle880
Asked:
brothertruffle880
3 Solutions
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

something along these lines, maybe?

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

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

End Sub

Open in new window


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

cheers, teylyn
0
 
brothertruffle880Author Commented:
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
0
 
Steven HarrisPresidentCommented:
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

0
 
byundtCommented:
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

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now