Monday, October 31, 2011

How to delete a row based on condition?

I created a macro to delete an entire row in an Excel spreadsheet based upon a specific given condition. I've seen a few others code for this same problem and I thought they overthought it and wrote a unnecessarily long code. Here is my quick and easy solution. Let's say I have a few cells in column A named "FINAL_BODY" where I want to delete the entire row.

Sub Delete_FINALBODY_Rows()

Dim r As Long

For r = Range("A" & Rows.Count).End(xlUp).Row to 1 Step -1

If UCase(Left(Cells(r, "A").Text, 10)) = "FINAL_BODY" Then Rows(r).Delete

Next r

End Sub

Sunday, October 23, 2011

Dilbert Comic Strip: How to do Formulas in Excel

I saw these Dilbert strip today in the Sunday paper and thought I would share it as it mentions Microsoft Excel. (Click on the image to see the full strip)

Wednesday, October 19, 2011

VBA to Export Properties to Excel

Excel is a powerful program and I often find myself using it in conjunction with other software programs. As a CAD engineer, I am often making parts lists in Micosoft Excel based upon 3D data files in CAD software, such as CATIA V5. Lucky for me CATIA has its own catscript VBA editor which allows you to write macros which can automatically export data from Catia to Excel, a huge timesaver!

I recently created an example where I take several key properties from a 3D CAD model part including the mass, thickness, material, and part number and export them into a nice little spreadsheet. I can then format the spreadsheet from within my Catia macro. Every little thing I can do to cut back on those extra mouse clicks (call me lazy I guess).

This code can also help you to learn how to program in visual basic which is very helpful in automating repetitive tasks. Here is a small sample:

'to excel
Dim Excel As Object
Dim workbooks As workbooks
Dim workbook As workbook
Dim Sheets As Object
Dim Sheet As Object
Dim worksheet As Excel.worksheet
Dim myworkbook As Excel.workbook
Dim myworksheet As Excel.worksheet

On Error Resume Next
Set Excel = GetObject(, "EXCEL.Application")
If Err.Number <> 0 Then
Err.Clear
Set Excel = CreateObject("EXCEL.Application")
Else
Err.Clear
MsgBox "Please note you have to close Excel", vbCritical
Exit Sub
End If
Excel.Visible = True

Set workbooks = Excel.Application.workbooks

'row one
Excel.Cells(1,1)="Part Number"
Excel.Cells(1,2)="Thickness"
Excel.Cells(1,3)="Material"
Excel.Cells(1,4)="Mass"

Download the full code here. You can also import Excel files into programs such as CATIA but that is a post for another day!