Solved

vba to fill cells

Posted on 2013-11-06
7
291 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:W.E.B
  • 3
  • 2
  • 2
7 Comments
 
LVL 49

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:W.E.B
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 49

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:W.E.B
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:W.E.B
ID: 39627927
Thank you
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
location range 4 22
First Blank Cell in a range 7 34
Excel Formula 4 28
Consolidate xl 2010 worksheets with text 2 20
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 …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

914 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

19 Experts available now in Live!

Get 1:1 Help Now