Need to count rows in the copied range, or rows in the clipborad

[frank_m]

Before pasting rows to a new selection, is there anyway that I can count the number of rows that will be pasted?
Either the number of rows (within the copy mode dotted lines), or within the clipboard ?

Thanks

[Kenneth Hobs]

While you could, it might be easier to count the number of lines that the clipboard contains. This would be the number of rows or it might just be plain text.

[frank_m]

HI Kenneth,

I appreciate your response. - I tried modifying some code I saw at the Pearson site, in an attempt to count the lines in the clipboard, but was suffering errors that suggested I may not have the reference set to the MS Forms 2.0 library, (when I did have it), so I don't know what I was doing wrong.

I apologize for not saving the code to show you, as I got a bit hasty in disposing of it when I solved my issue another way, that does noit require counting rows.

Best Regards,

Frank

[Kenneth Hobs]

The Forms dataobject does not really fit your need in this case. This will show the number of lines for text or the number of rows for a Selection. The selection method will not give the correct results if a range is copied or cut and then another range is selected.

Function getClipboard()
'Add Reference:   'Reference: Microsoft Forms xx Object
    Dim MyData As DataObject
     
    On Error Resume Next
    Set MyData = New DataObject
    MyData.GetFromClipboard
    getClipboard = MyData.GetText
End Function

Sub Test_getClipboard()
  Dim s As String, lineCount As Long
  
  If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then
    MsgBox "Row Count: " & Selection.Rows.Count
    Exit Sub
  End If
  
  s = getClipboard
  lineCount = UBound(Split(s, vbCrLf)) + 1
  MsgBox s, vbInformation, "Line Count: " & lineCount
End Sub