Solved

vba to fill cells

Posted on 2013-11-06
7
301 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
[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
  • 2
  • 2
7 Comments
 
LVL 51

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 51

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

738 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