Fill Blanks

Hi VBA experts,

I want some VBA code to replace the Fill blanks technique described on the attached link

http://www.j-walk.com/ss/excel/usertips/tip040.htm

I know I could use the code from Debra Dalgleish site but wanted to play with some VBA code myself and ask for some pointers

http://www.contextures.com/xlDataEntry02.html#FillProg

Here's what I've come up with so far. How would I trap the errors. Only using On error sesume next. Plus can I shorten the code

Also can I adapt it to do more than one Column at a time

Please Login or Register  to view this content.

Thanks

VBA Noob

Hi Carim,

Cells.Select will select all the cells.

Range(ActiveCell, Cells(Rows.Count, ActiveCell.Column).End(xlUp)).Select

Selects the active cell down to the last cell in column with an entry.

I was looking for tips on error trapping e.g No blank cells plus looking at ways to give the user the option to select more than one column.

If that can't be done then maybe run on the first column and then at the end it pops up with an input box so the user can select the next column or press cancel if finshed.

Could you set the user column selection and then loop through each row

VBA Noob

Hi Matt,

Thanks for the reply.

I thought of the currentregion idea but there may be gaps in data.

I think I will have to do it Column by Column

VBA Noob

Originally Posted by VBA Noob

Hi Matt,

Thanks for the reply.

I thought of the currentregion idea but there may be gaps in data.

I think I will have to do it Column by Column

VBA Noob

Hi,

Why not take the selected range, asin
if a single cell is selected then the column from that point to column end (ie, last data),
if a range of mutltiple rows and multiple columns is selected then those columns for those rows,
and if a single row and multiple columns are selected then those columns from that row to each column end (last data).

This avoids having to enter an annoying userbox range etc.

hth
---

Hi Bryan,

Selecting the range manually is the issue. If your blanks are bewteen A2:A10000 it's quicker to run it on each column then trying to find the last cell for each column

VBA Noob

Originally Posted by VBA Noob

Hi Bryan,

Selecting the range manually is the issue. If your blanks are bewteen A2:A10000 it's quicker to run it on each column then trying to find the last cell for each column

VBA Noob

Yes,

hence the reason for the suggestion, the code can recognize three options,

2, the unlikely 'Range' (ie A2:D500) and do the range,
3, the possible multi-column (A2:D2) where it does columns A to D
1, the probable single cell, (A2) where it does column A from 2 to the last data.

For multi-column (or single column) you might decide the last data item for that column, or the last data in a range of columns.

The last cell for each column can be from the 'xlUp' from that or from another column or columns.

hth
---