PDA

View Full Version : Solved: Looping Through Cells



bopo
10-24-2008, 02:59 PM
Hi ppl

Basically I have quite a large spreadsheet with a couple of thosands rows (or records :)), anyway not every cell contains data, I need a loop which will loop through each cell from say A1 to V4500 and insert "N/A" if the cell is blank, but im not quite sure on the coding as I havent touched vba for a while.

Help appreciated.

Bob Phillips
10-24-2008, 03:26 PM
Dim LastRow As Long

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A1").Resize(LastRow).SpecialCells(xlCellTypeBlanks).Value = "N/A"
End With

bopo
10-24-2008, 04:18 PM
Thanks for the help, however I keep getting a run time error when trying to execute:

Run-time error '1004'

No Cells were found.

Also this is the piece of code the compiler is getting stuck on

.Range("A1").Resize(LastRow).SpecialCells(xlCellTypeBlanks).Value = "N/A"

Bob Phillips
10-24-2008, 04:34 PM
That means there are no blanks, so nothing to insert.

mdmackillop
10-24-2008, 04:51 PM
Hi Bob
A1 to V4500

bopo
10-24-2008, 04:54 PM
Hi Bob
A1 to V4500

Ahh, as I didn't really understand the code, I presumed it automatically stopped after the next row was blank, anyways I have inserted the other range value and it works great :thumb