Need help with one line of code

B

Bob

Prior to performing a search and replace operation on column C (where I’m
searching for blank cells and replacing them with “n/aâ€), I want to first
select the range in column C based on the last row of data contained in
column A. Note: the number of rows containing data (based on column A) will
always be GREATER than the number of rows containing data (based on column
C). In other words, there will always be more data-containing cells in
column A than in column C.

Bob Phillips was kind enough to provide me with the following three lines of
code for determining the last row of data (based on column A) and then
performing a copy/paste operation on column J:

Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("J2").AutoFill Range("J2").Resize(iLastRow - 1)

Now, using Bob’s 3rd line of code, I’ve been trying to modify it (I’m a VBA
novice) so that it will select the appropriate number of cells in column C,
based on the number of data-containing rows in column A:

Dim iLastRow As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Range("C2").Select Selection.Resize (iLastRow - 1)

Unfortunately, when I execute the code I receive an error message. Can
someone tell me the correct syntax for the 3rd line of code? I would greatly
appreciate it.

Thanks,
Bob
 
G

Gary Keramidas

really shouldn't have to select, but here it is

Range("C2:C" & iLastRow - 1).Select
 
M

Mike Fogleman

You don't need to select, this will replace blank cells in column C as far
down as there is data in column A:

Dim iLastRow As Long, rng As Range, c As Range
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set rng = Range("C2:C" & iLastRow)
For Each c In rng
If c.Value = "" Then c.Value = "n/a"
Next

Mike F
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top