PDA

View Full Version : Excel 2007 find last column in table



Jeff1959
03-16-2011, 09:36 AM
Hi all, I'm trying to write code to find the last column in a dynamic table created from SQL. What ultimately I want to do is insert a column programmatically at the end of the table and subtract the last column from the next to last column.
I'm afraid that this is beyond my current VBA skills so any help would be appreciated.

Jeff

GTO
03-16-2011, 09:56 AM
Here's one way. Note that I used the sheet's CodeName.

Option Explicit

Sub exa()
Dim lLRow As Long, lLCol As Long
Dim rngFoundCell As Range

With Sheet1
Set rngFoundCell = RangeFound(.Cells)
'// In case an empty sheet //
If rngFoundCell Is Nothing Then Exit Sub

lLRow = rngFoundCell.Row

Set rngFoundCell = RangeFound(.Cells, , , , , xlByColumns)

lLCol = rngFoundCell.Column
End With

End Sub

Function RangeFound(SearchRange As Range, _
Optional FindWhat As String = "*", _
Optional StartingAfter As Range, _
Optional LookAtTextOrFormula As XlFindLookIn = xlValues, _
Optional LookAtWholeOrPart As XlLookAt = xlPart, _
Optional SearchRowCol As XlSearchOrder = xlByRows, _
Optional SearchUpDn As XlSearchDirection = xlPrevious, _
Optional bMatchCase As Boolean = False) As Range

If StartingAfter Is Nothing Then
Set StartingAfter = SearchRange(1)
End If

Set RangeFound = SearchRange.Find(What:=FindWhat, _
After:=StartingAfter, _
LookIn:=LookAtTextOrFormula, _
LookAt:=LookAtWholeOrPart, _
SearchOrder:=SearchRowCol, _
SearchDirection:=SearchUpDn, _
MatchCase:=bMatchCase)
End Function

mancubus
03-16-2011, 10:02 AM
if it is an excel table (when you click on a cell "table tools" tab appear next to QAT) and range is, say A1:G500


ActiveSheet.ListObjects("MyTable").Resize Range("$A$1:$H$500")
Range("H2").FormulaR1C1 = _
"=MyTable[[#This Row],[ColumGHeader]]-MyTable[[#This Row],[ColumFHeader]]"



you can do this easily by macro recorder.

mdmackillop
03-16-2011, 01:19 PM
Sub SubtractData()
Dim c As Range
Set c = Cells(1, Columns.Count).End(xlToLeft)
Set c = Range(c, c.End(xlDown))
c.Offset(, 1).FormulaR1C1 = "=RC[-1]-RC[-2]"
c.Offset(, 1).Value = c.Offset(, 1).Value
End Sub

Jeff1959
03-22-2011, 06:28 PM
GTO this worked great thanks for your help