Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel VBA - Testing a cell for any type of content

Posted on 2013-12-08
4
Medium Priority
?
417 Views
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.
0
Comment
Question by:brothertruffle880
4 Comments
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 664 total points
ID: 39705123
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
 

Author Comment

by:brothertruffle880
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
0
 
LVL 18

Assisted Solution

by:Steven Harris
Steven Harris earned 668 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

0
 
LVL 81

Accepted Solution

by:
byundt earned 668 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

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

916 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