Solved

If cell is blank

Posted on 2013-11-29
11
231 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
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…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

758 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