New to Excel VBA - Entering data into a range of blank cells

[rcbricker]

Hello!

I have a spreadsheet with data in columns A & B. Row 6 is my header row with additional headers (other than A & B) out to Column R. the data down A & B goes to row 20. The number of Columns (other than A & B) and the number of rows (Besides 6) will vary from sheet to sheet. The final Column is a total column,

I am trying to figure out how to do the following:

1. Find last row
2. Find Last Column
3. Set a range for A6:<last column <(offset(0,-1) to leave the total column blank>, last row <(offset(1,0) to leave the header row untouched.>
4. Enter 0 into all empty cells in the above range except for the cells in columns A & B.
5. Enter values for the now only empty cells in my total (Last Column) column.

I have the following, but it only puts 0 in the last row all the way to last column but not in any of the rows above last row to row 6 (headers).

note comments contain code that I have "turned off" or re-written while trying to figure out the issue.

Thanks

Rich

Solved it myself lol using a for each cell in rng

wow struggled for a couple days and it was so simple.

Rich

 

[Markbnj]

Thanks for posting your solution, and welcome to the forum.

 

[Tweak155]

Welcome to AT! I do VBA for a living right now so I've learned a lot of tricks over the years.

Your suggested solution doesn't sound like an optimized one unless your range is rather small. In general, the fastest way to calculate a last row is to know the Column which will always have the last set of data.

There are built in functions in excel that try to calculate this for an entire sheet, but it's not accurate.

If you know the column, this is the best way I've consistently used to identify a last row:

With ThisWorkbook.Sheets("What Sheet")
    LastRow = .Cells(.Rows.Count, COLUMN_WITH_LAST_DATA).End(xlUp).Row
End With

To get cells with values vs cells with blanks, the SpecialCells function is your best friend, but also sometimes tricky to work with as it has a few flaws. But, assuming your range has more than one cell in it and it will always return an answer, you can do the following:

Cells with values:

ThisWorkbook.Sheets("What Sheet").Range("A1:C100").SpecialCells(xlCellTypeConstants)

The above will return any cells with values in them, and you can For Each through it.

To get blanks:

ThisWorkbook.Sheets("What Sheet").Range("A1:C100").SpecialCells(xlCellTypeBlanks)

Those should get you started! When working with SpecialCells, you have to keep in mind 2 things. 1 - It doesn't work properly if your range is only one cell (I.E Cells(1,1) or Range("A1"). And 2 - If it doesn't find a cell that meets your criteria, it throws an error message that you can't handle without extra work. So in general, you'll need an On Error Resume Next around it (followed by On Error Goto 0). I wrote a function that handles special conditions for SpecialCells to make my life easier.