PDA

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

SamT
09-21-2016, 06:40 AM
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

SamT
09-22-2016, 06:22 AM
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