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
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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.