copy column

[oleg_v]

HELLO
I need some help with copy in excel:
i need to copy the data from last not empty cell in in column "b"
to the first empty cell in column"a" starting with "a4"


thanks

[GTO]

Try:


Option Explicit

Sub exa()
Dim wks As Worksheet
Dim rngLRow As Range
Dim rngFAvailRow As Range

'//Chaneg sheetname to suit //
Set wks = Worksheets("Sheet1")

With wks
Set rngFAvailRow = RangeFound(.Range("A4:A" & Rows.Count))
If rngFAvailRow Is Nothing Then
Set rngFAvailRow = .Range("A4")
Else
Set rngFAvailRow = rngFAvailRow.Offset(1)
End If

'// Presumes a header row, adjust to suit.//
Set rngLRow = RangeFound(.Range("B2:B" & Rows.Count))
If rngLRow Is Nothing Then
MsgBox "Nothing found to ""copy""", 0, vbNullString
Exit Sub
Else
rngFAvailRow.Value = rngLRow.Value
End If
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:=False)
End Function