Unanswered: VBA to Automate the "Fill" Menu Option?
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!
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
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.
' Enter a variable for the column to check for the last used Row
' I used column 2 per your sample
nCol = 2
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 = ""
.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
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.
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.