How to select a row data before blank cell?

Question

For i = 3 To 50
     If lngRow = Range("A" & i) Then
        Range("A1:EN3").Rows(i).Copy
        Range("A1:EN3").Columns(strCol).Offset(, 1).PasteSpecial Transpose:=True
        Range("A1:EN3").Rows(1).Copy
        Range("A1:EN3").Columns(strCol).PasteSpecial Transpose:=True

    Exit For
    End If
Next i

Hello I have written code for selecting the row and paste it into column wise it is working correctly but my problem is here i am giving range as Range("a1:en3") but every time it may be more the values than this so is it possible to copy the row data before blank cell like how we can copy the column before blank cell i,e Range(rng, rng.End(xlDown)).Copy.

thank you can you give me fulll code such as paste special and all because it is pasting row as row i want to paste row into column — M_S_SAJJAN
You can write a function to determine where that first blank cell lies and then use that value in your range. Not terribly efficient but it should work. — jpsfer

jpsfer jpsfer · Accepted Answer · 2012-09-18T08:21:28

Function firstblank()
   i=1
   While (worksheets("WORKSHEETNAMEHERE").cells(i,1).value<>"")
     i=i+1
   wend
   return i
End function

Not totaly sure I understand your question but pasting the transposed values should be as easy as:

Range("T5").Select 
Selection.PasteSpecial Transpose:=True

(i would not define a range bigger than one cell to write the values; if you give more than one cell excel will require your target to have the exact dimensions of what you are trying to write)

How to select a row data before blank cell?

1 Answers