copying between cols using autofilter

  • Thread starter Thread starter Gaurab
  • Start date Start date
G

Gaurab

Hi,
I am relatively inexperienced with Excel 2k autofilter option. I have
a very large worksheet, with approx 20k number of rows, and 8 cols. I
am interested in 2 cols which are adjacent to each other on the
worksheet.
The first col has data (text only), the 2nd col has a certain no. of
blank fields. It is the blank cells that I am interested in.

I use the auto filter option and using the pull-down list on the 2nd
col select and select only the blank cells. I now wish to copy the
contents of the the first col to the 2nd col, where there are blank
fields, so in effect renaming that cell with the contents of the cell
in the first col.

Currently, I cannot do it as Excel only lets me copy contents of rows
which are directly next to each other.

Any help would be appreciated and thanks in advance.

Gaurab
 
Why not just do it without the autofilter. If you select a range in col C
then blanks will be filled in from col B.

Sub fillinblanks()
For Each c In Selection
If c = "" Then c.Value = c.Offset(, -1)
Next
End Sub
 
Hi Don,
Is the snippet of code to run a macro? Where do I actually run the
code? Sorry, to a be a total greehorn about this.

Thanks,
 
Another way without the filter:

Select that 2nd column.
Edit|goto|special, click on Blanks
in the active cell: type = and point to the cell on the same row that you want
copied.

hit ctrl-enter instead of just enter and the formerly blank cells will be filled
with formulas that point at the data cell.

Then you can convert them to values (select the column|copy|paste special
values)

Debra Dalgleish has nice pictures at:
http://www.contextures.com/xlDataEntry02.html

But in her example, she's filling cells vertically.

=======
Or when your data is filtered to show just the blank cells, you can type the
formula for one cell and drag down through the range. Only the visible cells
(formerly blank) will be filled with the formula.

And then convert to values (if you want).
 
Thanks Dave,
That did the trick, and thanks to others who replied.


Another way without the filter:

Select that 2nd column.
Edit|goto|special, click on Blanks
in the active cell: type = and point to the cell on the same row that you want
copied.

hit ctrl-enter instead of just enter and the formerly blank cells will be filled
with formulas that point at the data cell.

Then you can convert them to values (select the column|copy|paste special
values)

Debra Dalgleish has nice pictures at:
http://www.contextures.com/xlDataEntry02.html

But in her example, she's filling cells vertically.

=======
Or when your data is filtered to show just the blank cells, you can type the
formula for one cell and drag down through the range. Only the visible cells
(formerly blank) will be filled with the formula.

And then convert to values (if you want).
Click to expand...

Gaurab
 
Back
Top