Solved

vba to fill cells

Posted on 2013-11-06
7
290 Views
Last Modified: 2013-11-06
Hello,
can you please help with a vba code (I need to sue in excel 2007) that can find last column used / last row used
then fill blank / empty cells with 0

Please see sample attached.
sample.xlsx
0
Comment
Question by:Wass_QA
  • 3
  • 2
  • 2
7 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
ID: 39627585
Hi,

pls try
Sub Macro()
Application.ScreenUpdating = False
Set myRange = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, Columns.Count).End(xlToLeft))

For Each c In myRange
    If c.Text = "" Then c.Value = 0
Next
Application.ScreenUpdating = True
End Sub

Open in new window

Or the version with SpecialCells which is quicker
Sub Macro()
Application.ScreenUpdating = False
Set myRange = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, Columns.Count).End(xlToLeft))

For Each c In myRange.SpecialCells(xlCellTypeBlanks)
    c.Value = 0
Next
Application.ScreenUpdating = True
End Sub

Open in new window

Regards
0
 

Author Comment

by:Wass_QA
ID: 39627594
Thanks,

I get error

variable not defined
Set myRange =

thakns
0
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 39627607
Try simply

Sub fillzeros()
ActiveSheet.UsedRange.SpecialCells(xlCellTypeBlanks).Value = 0
End Sub
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 48

Assisted Solution

by:Rgonzo1971
Rgonzo1971 earned 125 total points
ID: 39627614
Hi,

Sub Macro()
Dim myRange As Range
Dim c As Range
Application.ScreenUpdating = False
Set myRange = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, Columns.Count).End(xlToLeft))

For Each c In myRange.SpecialCells(xlCellTypeBlanks)
    c.Value = 0
Next
Application.ScreenUpdating = True
End Sub

Open in new window

0
 

Author Comment

by:Wass_QA
ID: 39627650
Thanks Rgonzo1971,
it worked.

I made a mistake with the columns,
I have a gab between 2 sections (see attached sample)
in sample attached, can it stop at Column "Y"

ie, stop at last column used before gab

I tried to use
Set myRange = Range(Cells(Rows.Count, 1).End(xlUp), Cells(1, Columns.Count).End(xlToRight))

but didnt work,

thanks again,
sample.xlsx
0
 
LVL 43

Accepted Solution

by:
Saqib Husain, Syed earned 125 total points
ID: 39627683
Try

Sub fillzeros()
Range(Range("A2"), Cells(Range("A1").End(xlDown).Row, Range("A1").End(xlToRight).Column)).SpecialCells(xlCellTypeBlanks).Value = 0
End Sub
0
 

Author Closing Comment

by:Wass_QA
ID: 39627927
Thank you
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

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

18 Experts available now in Live!

Get 1:1 Help Now