Solved: Ranges are the Worst...

[magelan]

So, the age old question of "How do I get the Range I want, because ranges do not work logically or simply".

I'm trying to grab the last row of Column C but of course this doesnt work [because ranges are a headache to work with]

[vba]
With ActiveSheet
listLength = .Cells(.Rows.Count, "C").End(xlUp).Row
End With

cellsRange = Range("C2:C" & listLength)
[/vba]

i also tried
[vba]
With ActiveSheet
listLength = .Cells(.Rows.Count, "C").End(xlUp).Row


cellsRange = Range("C2", Cells(.Rows.Count, "C").End(xlUp))

end with
[/vba]

[magelan]

Apparently I had forgotten to put "set" in there...

Nevermind.

[snb]

[VBA]
sub M_snb()
lastcell=sheets(1).cells(rows.count,3).end(xlup).address
end sub
[/VBA]

[magelan]

Heres another one...

[vba]
Dim destinationRange As Range
Range("J9:Q9").Select
Set destinationRange = Range(Selection, Selection.End(xlDown))

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & inputfile, Destination:=Range("$A$8"))
.Name = "inputme"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = True
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
'Fill in Formulas for Data
Application.Goto Reference:="R9C10"
Range(Selection, Selection.End(xlToRight)).Select
Selection.AutoFill Destination:=destinationRange
[/vba]

Of course, the code decides to fill THE ENTIRE WORKBOOK rather than just the used cells.

What i'm trying to accomplish is to autofill from J9:Q9 to J[end]:Q[end]

[snb]

Look in the VBA helpfiles, lemma 'currentregion'.


PS. I hope you realise a worksheet is a range, a row is a range, a column is a range, a cell is a range....

[Aussiebear]

[QUOTE=magelan]"... because ranges do not work logically or simply".

Then you won't like excel. As snb has indicated from a single cell to multiple combinations of cells they are all ranges.

I'm trying to grab the last row of Column C but of course this doesnt work [because ranges are a headache to work with]

Have a good look at naming your ranges, and creating dynamic ranges.