Solved

Add data at empty cell

Posted on 2011-03-15
8
316 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
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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 500 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

Problems using Powershell and Active Directory?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel VBA - UserForm Label Caption to show recordset data dynamically 12 21
Left trim cells in column A Excel vba 2 32
And OR formula 5 22
Excel VBA - Constants 4 16
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 …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
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.

778 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