?
Solved

If cell is blank

Posted on 2013-11-29
11
Medium Priority
?
276 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
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.

 
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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
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…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

800 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