Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

If cell is blank

Posted on 2013-11-29
11
Medium Priority
?
280 Views
Last Modified: 2013-11-29
Can an expert provide me with the code where the cell I am on is blank so I need to go to next oiece of code.

Thanks
0
Comment
Question by:Jagwarman
[X]
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
  • 5
  • 5
11 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 1600 total points
ID: 39685397
Try either of these sequences
if activecell<>"" then
.
.
end if

Open in new window


if activecell="" then
.
.
end if

Open in new window

0
 
LVL 11

Assisted Solution

by:SeanStrickland
SeanStrickland earned 400 total points
ID: 39685399
Can you give a little more information on what you're looking for?  Until then, here's a quick sample of some code that does this.  You can insert this into your worksheet's module.  (right-click your worksheet tab and click view code).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If IsEmpty(Target) Then
    MsgBox "Cell is blank... do whatever."
End If
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 39685400
Sorry need to clarify this.

If the cell I am on is not blank then copy from that cell down to last non blank cell and also copy across rows. i.e. if I am on A50 and A70 is last non blank copy A50:X70 because Column X71 and beyond are blank cells

But if A50 is Blank then put out Msg" No Date"

Thanks
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 1600 total points
ID: 39685409
If activecell <> "" then
    activecell.currentregion.copy
else
    Msgbox " No Date"
endif
0
 

Author Closing Comment

by:Jagwarman
ID: 39685426
Thanks
0
 

Author Comment

by:Jagwarman
ID: 39685453
Hi ssaqibh

Love the code but I found a slight fault in that your code stops at first blank cell in the row but I need to get all data in that row. i.e. if C4 is blank but D4 through S4 are not blank and T4 is blank and U4 through AC4 are not blank I need to copy from C4 through AC4

Thanks
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39685491
If it is one row then try

range(activecell,cells(activecell.row,columns.count).end(xltoleft)).copy
0
 

Author Comment

by:Jagwarman
ID: 39685531
Unfortunately it's not it can be many :-(
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39685551
If it is many then currentregion should work otherwise then columns.count should work
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39685555
Maybe you can try something like

If activecell <> "" then
    if activecell.currentregion.rows.count=1 then
        range(activecell,cells(activecell.row,columns.count).end(xltoleft)).copy
    else
        activecell.currentregion.copy
    endif
else
    Msgbox " No Date"
endif
0
 

Author Comment

by:Jagwarman
ID: 39685652
ok thanks ssaqibh
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
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…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
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…

636 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