Find lastrow & copy down

[koi]

Hi All,

I need help with how to find lastrow on sheet "temp" in column A, and copy it down how many rows according to number of input text box

can someone help me on this?

Thanks

 

[koi]

Hi,

I've managed to do it perfectly, please see below in case someone need similiar code

Sub test()

Dim i As Integer
Dim FirstEmptyRow As Long 
Dim paste_range As Range
Dim source_range As Range
Dim LastRow As Long

Dim UserInput As Long
UserInput = _
InputBox("How Many Times?") 'you can change the words in bracket as you wish

For i = 1 To UserInput 'any number you put in the box will be max number to loop

FirstEmptyRow = Worksheets("yoursheetname").Range("Q" & Rows.Count).End(xlUp).Row + 1 'you can change the column to any column
LastRow = Worksheets("yoursheetname").Range("Q" & Rows.Count).End(xlUp).Row
Set paste_range = Worksheets("yoursheetname").Range("Q" & FirstEmptyRow)
Set source_range = Worksheets("yoursheetname").Range("Q" & LastRow) 

source_range.Copy _
Destination:=paste_range

Next i 'this is to loop from 1 to userinput

End Sub

 

[BobBridges]

I keep two functions around for finding the last row in a table or worksheet. LastRow does an <End><Down> in a specified column:

' Return the last used row in a sheet, using the Range.End(xlDown) method
' (depends on there being no empty cells in the index column).  Supply the
' sheet object, the number of header rows and a column that will be
' unbroken from top to bottom, and I'll return the row number of the last
' used row.  This should work with no header rows, too.  If nHdr=0 and no
' data, returns 0.
Function LastRow(ows, Optional nHdr = 1, Optional cIdx = 1)
  If IsMissing(nHdr) Then nHdr = 1
  If IsMissing(cIdx) Then cIdx = 1
  Set oc = ows.Cells(nHdr + 1, cIdx)
  Select Case True
    Case oc.Value = "": LastRow = nHdr
    Case oc.Offset(1, 0).Value = "": LastRow = oc.Row
    Case Else: LastRow = oc.End(xlDown).Row
    End Select
  End Function

That works if there are no empty cells between the header and the end. If there are, LastRowBrk jumps down to one past the last used row in the worksheet and then does an <End><Up> in the specified column to the last non-empty cell:

' Return the last used row in a sheet even if your index column isn't unbroken from top
' to bottom, using SpecialCells(xlCellTypeLastCell).  Supply the sheet object and the
' index column.
Function LastRowBrk(ows, Optional cIdx = 1, Optional bUnhide = True)
  If bUnhide Then ows.Rows.Hidden = False
  LastRowBrk = ows.Cells.SpecialCells(xlCellTypeLastCell).Offset(1, 0).Row
  LastRowBrk = ows.Cells(LastRowBrk, cIdx).End(xlUp).Row
  End Function

Very handy to have these around, so I don't have to repeat the logic in every program.