Spreadsheet wrangling

Let’s say you have a bunch of Excel workbooks, and you need to combine them, or maybe just certain rows within them. Perhaps then you need to re-sort this data, and maybe look for duplicate rows and do something with them.

Here’s some VBA code that will help. I wrote something quite similar to this for work recently, to help massage some data produced by a product we’re using. I’m not posting the exact code because I probably don’t own it. But here’s the general idea:

' create an invisible excel object
set objExcel = CreateObject("Excel.Application")
objExcel.Visible = false 
objExcel.Interactive = false

' open a workbook and get a specific worksheet
set workbook = objExcel.Workbooks.Open(input_file)
set worksheet = objExcel.ActiveWorkbook.Worksheets("My Sheet")

' see how many rows are in use, select them, count them
set worksheet_range = worksheet.UsedRange
set worksheet_rows = worksheet_range.Rows
worksheet_row_count = worksheet_rows.Count

' copy a range
worksheet_range.copy

' paste a range
worksheet.range("A1").select
worksheet.pasteSpecial 

' sort a worksheet by a particular column
worksheet.range.sort("A:A")

' look for duplicated rows based on a cell value you know to be unique
for i = 1 to worksheet_row_count
	if worksheet.range("A" & i).value = worksheet.range("A" & i + 1).value then
		' found a duplicate
	end if
next

' save as, with prompt if needed
objExcel.DisplayAlerts = true
workbook.SaveAs(output_file)

Re-arrange those blocks until you have something that does exactly what you want.

I use the clipboard for moving ranges around, which is fine when they’re relatively small, but it’s not the quickest or most efficient way of doing things; you might want to set the cell values directly instead of cutting and pasting. I used the clipboard because it was handy in this specific instance to have the values selected still on the clipboard once the script had finished running.

Leave a Reply

Your email address will not be published. Required fields are marked *