Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Add data at empty cell

Posted on 2011-03-15
8
Medium Priority
?
321 Views
Last Modified: 2012-08-13
Hi Experts,

I would like to request Experts help create a macro to add empty cell (active rows) at column A to AX with “No Data” automatically. Hope Experts will help me to create this feature.



AddData.xls
0
Comment
Question by:Cartillo
[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
  • 3
  • 3
  • 2
8 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35135600
At Which row?

Sid
0
 

Author Comment

by:Cartillo
ID: 35135627
Hi Sid,

The empty cell can be found in any active rows from column A:AX
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35135629
Rather than create a macro, use this technique for placing a value in all empty cells in a selection.

Select the block of cells into which the value or formula is to be copied. Include any non-empty cells. Choose the menu command Edit->Go To, click Special, select Blanks, and click OK. Enter the formula into the formula entry box in the Formula Bar and press CTRL+ENTER.

If the formula is complex or already exists then copy it to the clipboard before selecting the cells. To copy a formula in a cell to the clipboard, select the cell, select the formula from the formula entry box in the Formula Bar, press CTRL+C, and press ENTER. It is important to press ENTER before selecting any cells as the selection process will change any formula currently being edited.

Note that any selected cells that are not part of the used range will not be included.

Kevin
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 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35135638
If you want a macro to do same:

Sub DoIt()
    Selection.SpecialCells(xlCellTypeBlanks).Select
    Selection.FormulaR1C1 = "No Data"
End Sub

Kevin
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35135648
Cartillo: Your thread says "Add data at empty cell" and the post says, "to add empty cell (active rows) at column A to AX with “No Data” automatically"

Which one do u want?

Sid
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 2000 total points
ID: 35135652
Even better:

Sub DoIt()
    Selection.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "No Data"
End Sub

Or, if you want to do the entire used range:

Sub DoIt()
    ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).FormulaR1C1 = "No Data"
End Sub

Kevin
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35135656
After reading Kevin's post, I think I understood what you want :)

Sid
0
 

Author Closing Comment

by:Cartillo
ID: 35142327
Hi Kevin/Sid

Thanks a lot for the help
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
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…

722 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