Solved

If cell is blank

Posted on 2013-11-29
11
273 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
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
 

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

Industry Leaders: 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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

688 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