Solved

If cell is blank

Posted on 2013-11-29
11
245 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
  • 5
  • 5
11 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 400 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 100 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
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 400 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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 tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

863 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now