View Full Version : [SOLVED:] Newbie help on row count as range
heathb
09-21-2016, 02:07 AM
Hi all, very new to this and would appreciate some basic help.
I have a excel file that the rows change daily. This file needs to have columns added and formulas copied to the last row.
I am doing this long route currently
Range("A4").SelectSelection.End(xlDown).Select
ActiveCell.Offset(-1, 8).Range("A1:G1").Select
Range(Selection, Selection.End(xlUp)).Select
Selection.FillDown
I presume there is something I can do with this type of code
Dim rowcount As Variant
Dim countvalue As Variant
Dim count As Long
but how would I fit this into
ActiveSheet.Range("$A$3:$EQ$2068").AutoFilter Field:=62, Criteria1:="17679" when column EQ might be 3158 rows next time
Hope this makes sense to you, and appreciate any help
Thanks
Try this
ActiveSheet.UsedRange.Offset(2).AutoFilter Field:=62, Criteria1:="17679"
FYI
Range assignments that use row and column numbers are done with the Worksheet "Cells" Property:
Cells(RowNum, ColNum) or Cells(RowNum, ColLetter)
Dim myRange As Range
Set MyRange = Cells(3, 5) 'Range("E3")
Set myRange = Cells(3, "E")
'Multiple Cell Ranges
Set MyRange = Range(Cells(1, 1), Cells(3, 5)) 'Range("A1:E3")
Finding the last non empty Row in a particular Column
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Finding the last non empty Column in a particular Row
Dim LastCol As Long
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
End (xlDown) and (xlToRight) find the cell before the first empty cell in the Row or Column.
A1 = 3
A2 = "a"
A3 = [empty cell]
A4 = 42
A5 = "San Fransisco"
Range("A1").End.(xlDown).Row Returns 2
heathb
09-22-2016, 05:00 AM
Hi SamT, thanks for getting back to me
With your comment below, is this possible to add to a copy function at all?
As a bad example
Range("D1", lastRow of col N).Select
Finding the last non empty Row in a particular Column
Dim LastRow As Long
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Formatting tags added by mark007
Many thanks
Something like this?
Dim LastRow As Long
Dim LastCol As Long
Dim PrevFormula As Range
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
Set PrevFormula = Cells(Rows.Count, "B").End(xlUp)
LastCol = PrevFormula.End(xlToRight).Column
'Alternate method. Use only one method
LastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Range(PrevFormula, Cells(LastRow, LastCol)).FillDown
Depending on how your worksheet is structured, this might work
Dim LastCell As Range
With Range("A1").CurrentRegion 'The rectangle including A1, bounded by an empty Column and an Empty Row
Set LastCell = .Cells(.Cells.Count)
End With
Range(Cells(Rows.Count, "B").End(xlUp), LastCell).FillDown
jdautel
09-22-2016, 06:40 AM
I may not know exactly what you're looking for but I think this will give you the range you're trying to select
Sub TryThis()
Dim lastrow As Long
lastrow = Cells(Rows.Count, "N").End(xlUp).Row
Dim myrange As range
Set myrange = range(Cells(lastrow, "N"), Cells(1, "D"))
myrange.Select
End Sub
heathb
09-23-2016, 02:49 AM
thanks SamT and jdautel
I will play with these suggestions
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.