VBA to Automate the "Fill" Menu Option?

Friz · #1 (**permalink**) 03-08-08, 18:47

Greetings,

I usually hang out over in the MS Access forum area, but I have to segway over to Excel for my current problem.

I have a number of spreadsheets (200+) which I must clean up (for lack of a better word) so that I can import it into my MS Access database.

My office is in the process of going through years' worth of data in spreadsheet form and getting it into a database for easier querying, etc...

Anyway... my problem is this:
* The spreadsheets are created from an automated system, the format of which is dictated by a higher authority, and I cannot request a change to the format
* The first column of the spreadsheet (TAC) is not fully populated... in other words, if the first 15 rows are associated with a particular TAC, the TAC is listed in the first record, and the remainder are blank until the next series of records with a new TAC appears (this'll make much more sense if you look at the attachment)
* Before I can import it into my database, I need to copy the TAC down through column A until I hit a new TAC.
* So far, I've been highlighting the applicable area, hitting CTRL-D, to copy it down, and then moving along.

This takes WAAAY too long.

The spreadsheet I uploaded is a small fraction of a "real" spreadsheet. The real ones have around 15-20K rows and 20-30 TAC's on each. And I've got around 200 spreadsheets to do.

This seems like it should be easy, and although I'm fairly handy with VBA in Access, today is my first foray into Excel VB. Needless to say, it's not going well.

My rough VB pseudo code is below....Think I'm on the right track, but obviously my syntax is pretty much imaginary at this point... If someone could point me in the right direction, I'd be very appreciative!

Sub Copy_TAC()
Dim CurCell As Object
For Each CurCell In Selection
' If the cell below the current cell is blank
' then copy the data to the cell below it
' else skip to the next cell
' endif
Next
End Sub

Thanks for any assistance!

-Dean (a.k.a. Friz)

savbill · #2 (**permalink**) 03-08-08, 23:38

This will fill down the values. I would set this up in an Addin file or hidden file with a button to activate the code. I've set this up in the attached file. When you open the attachment select 'Enable Macros' then you'll see the attached 'TAC Fill Down' Toolbar has 2 buttons to Run and close the file.

Code:

Sub TACFillDown()

' Enter a variable for the column to check for the last used Row
' I used column 2 per your sample
nCol = 2

With ActiveSheet
  lRow = .Cells(65535, nCol).End(xlUp).Row
  
  For i = 2 To lRow
    strCell = Trim(.Cells(i, 1))
    If Len(strCell) > 0 Then
      strCurTAC = strCell
      strCell = ""
    End If
    .Cells(i, 1) = strCurTAC
  
    ' Selects every 10th row So you can see operation
    ' You can comment out the next line its optional
   If i Mod 10 = 0 Then .Cells(i, 1).Select
    
  Next
End With

End Sub

The workbook is hidden so you will see the toolbar but not the workbook worksheet. Open the VB Editor to view the code contained in the file.

Friz · #3 (**permalink**) 03-09-08, 00:13

Bill,

Bless you!!!!

This is quite impressive, and the button is an added bonus that I wasn't expecting!

I've been messing around with this since around 11 this morning and finally posted out of sheer desparation.

There is a much bigger learning curve than I expected coming from Access VBA. I suspect I will be going to the store tomorrow to purchase a book...

Thanks again for your assistance. It is very much appreciated indeed!

-Dean

savbill · #4 (**permalink**) 03-09-08, 21:23

Dean,

you have an advantage already knowing Access VB. You need to learn the Excel Object Model and a few tricks and you'll be coding Excel with no problem. I first learned Excel VB then had to learn Access when I was assigned an Access project. I agree it is a good idea to pick up a book and read to learn new skills. Also remember the F1 key is your friend.

Internet Services

Web Development

Digital Marketing

Consumer Tech