Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • Last Modified:

Add data at empty cell

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
Cartillo
Asked:
Cartillo
  • 3
  • 3
  • 2
1 Solution
 
SiddharthRoutCommented:
At Which row?

Sid
0
 
CartilloAuthor Commented:
Hi Sid,

The empty cell can be found in any active rows from column A:AX
0
 
zorvek (Kevin Jones)ConsultantCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zorvek (Kevin Jones)ConsultantCommented:
If you want a macro to do same:

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

Kevin
0
 
SiddharthRoutCommented:
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
 
zorvek (Kevin Jones)ConsultantCommented:
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
 
SiddharthRoutCommented:
After reading Kevin's post, I think I understood what you want :)

Sid
0
 
CartilloAuthor Commented:
Hi Kevin/Sid

Thanks a lot for the help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now