Solved

Excel VBA - Testing a cell for any type of content

Posted on 2013-12-08
4
377 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
Ingeborg Hawighorst earned 166 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 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

0
 
LVL 81

Accepted Solution

by:
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

0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

786 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