PDA

View Full Version : Struggling wth VBA, trying to specify paste destination



plasteredric
06-11-2018, 01:59 PM
I have the following code as part of a larger process. I'm trying to specify the cell where a range of data is to be pasted (CompleteSheetDestRange) and also check/confirm that the rest of the row is blank, however it doesn't seem to work.

Any help appreciated





Dim CompleteSheetLastCellOfFirstColumn As Range
Dim CompleteSheetDestRange As Range



'FIND LAST CELL WITHIN FIRST COLUMN OF COMPLETE SHEET
Set CompleteSheetLastCellOfFirstColumn = Sheets("Complete").Range("B" & Rows.Count).End(xlUp)


'SET COMPLETE SHEET DESTINATION POINT
Set CompleteSheetDestRange = CompleteSheetLastCellOfFirstColumn.Offset(1, 0)

'VALIDITY CHECK - CHECK DESTINATION RANGE START ROW IS BLANK
If Not CompleteSheetDestRange.row Is Nothing Then
MsgBox "Complete Sheet Destination Row is not blank, Unable to Transfer Data", 0, "Validity Check Error"
Application.Calculation = xlCalculationAutomatic
Exit Sub
End If

SamT
06-11-2018, 04:40 PM
'FIND LAST CELL WITHIN //FIRST// Second ("B") COLUMN OF COMPLETE SHEET
Set CompleteSheetLastCellOfFirstColumn = Sheets("Complete").Cells(Rows.Count, "B").End(xlUp)


'VALIDITY CHECK - CHECK DESTINATION RANGE START ROW IS BLANK
If WorksheetFunction.CountA(CompleteSheetDestRange.EntireRow > 0 Then


'FIND NEXT TRULY EMPTY ROW
Do While WorksheetFunction.CountA(CompleteSheetDestRange.EntireRow > 0
Set CompleteSheetDestRange = CompleteSheetDestRange.Offset(1, 0)
Loop