PDA

View Full Version : [SOLVED] Need to count rows in the copied range, or rows in the clipborad



frank_m
04-14-2011, 06:41 AM
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
04-14-2011, 08:20 AM
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
04-14-2011, 03:53 PM
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
04-14-2011, 06:04 PM
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