VBA Macro

Question

Hi,&nbsp;&nbsp;Please let me know if I am missing something.&nbsp;&nbsp;'Find the next available row in the destination sheetnextRow = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row + 1&nbsp;Instead of going to the first row, its going to the second row.&nbsp;Please see attached screencapture.&nbsp;&nbsp;Thanks for the help!

hansvogelaar · Answer

djreyes&nbsp;
If the entire column is still empty, destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp).Row will be 1, so if you add 1 you end up with 2.
You could use
&nbsp;
    Dim rngLastCell As Range
    Set rngLastCell = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp)
    If rngLastCell.Value = "" Then
        nextRow = 1
    Else
        nextRow = rngLastCell.Row + 1
    End If

djreyes · Answer

I tried and it didnt work, should I add it like this?

' Find the next available row in the destination sheet
Dim rngLastCell As Range
Set rngLastCell = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp)
If rngLastCell.Value = "" Then
nextRow = 1
Else
nextRow = rngLastCell.Row + 1
End If

hansvogelaar · Answer

djreyes&nbsp;
The code that I posted should replace the code that you included in the first post. How you use nextRow is up to you.
The code calculates nextRow correctly - I tested it. What is the problem you're having with it?

djreyes · Answer

Its still not going to the first row.Please see below what I have added,' Dim rngLastCell As RangeDim rngLastCell As RangeSet rngLastCell = destinationSheet.Cells(destinationSheet.Rows.Count, 1).End(xlUp)If rngLastCell.Value = "" ThennextRow = 1ElsenextRow = rngLastCell.Row + 1End If' Set the destination range for column A (first column)Set destinationRange = destinationSheet.Cells(nextRow, 1)' Add the value from cell D10 to the destination range, formatted as DD-MMM-YYYYdestinationRange.Value = Format(sourceSheet.Range("D10").Value, "dd-mmm-yyyy")' Set the destination range for column B (second column)Set destinationRange = destinationSheet.Cells(nextRow, 2)' Add the value from cell D9 to the destination rangedestinationRange.Value = sourceSheet.Range("D9").Value' Set the destination range for the transposed values (starting from column C)Set destinationRange = destinationSheet.Cells(nextRow, 3)' Transpose the values from the source range to the destination rangenumValues = sourceRange.Rows.CountFor i = 1 To numValuesdestinationRange.Offset(0, i - 1).Value = sourceRange.Cells(i).ValueNext i' Clear the contents of input cellssourceSheet.Range("D9:D10").ClearContentssourceRange.ClearContents

hansvogelaar · Answer

djreyes&nbsp;
Could you attach a sample workbook that demonstrates the problem (without sensitive data), or if that is not possible, make it available through Google Drive, OneDrive, Dropbox or similar?

Forum Discussion

VBA Macro

5 Replies

Resources