Detecting the size of data on the clipboard

[matty1973]

Hi,

I am trying to write a macro to paste the data that has been copied onto the clipboard into my worksheet. However I want to paste it in different ways depending on what data is there.

Any help is much appreciated. This is what I have so far:



Sub ImportTissue()

Sheets("Tissue").Select

If "dimensions of clipboard data are 5 cells by 5 cells" Then

Range("E11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Else

Range("P11").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End If

End Sub

 

[Gerald Higgins]

I don't know if you can get a macro to interrogate the clipboard as you suggest.
Perhaps you can.
But IF you can't, then a compromise might be to get the macro to paste the clipboard contents into a totally blank area in the workbook, and then you can use fairly standard functionality (all within the macro) to assess the size of the non-blank range, and then act accordingly. You will want to include code to clear out this area perhaps both before and after you paste the clipboard into it.

 

[GTO]

How did the data get onto the clipboard?

 

[matty1973]

The spreadsheet I'm writing is used by other people who need to paste in some data from their own sheet. However even with clear instructions they don't always copy the correct data - sometimes they copy too large an area.

So I'm hoping that if I can detect that it is too large then I can work out which is the correct part and just paste that in.

Thanks for the idea of pasting to a new sheet - that might work because the area required is close to a cell with a # in it so it should be identifiable. The only problem is that some of the cells are merged so pasting them into a new sheet is not straightforward.

 

[Blade Hunter]

You could populate an array like a virtual table or something then count the elements.

 

[Fred916]

Blade Hunter,

I like your idea of populating an array then counting the elements. I'm mediocre at macros. Can you provide a sample macro?


Here is what I'm trying to do:
1. Copy text to clipboard (from 1 to 20 rows or so; and 8 columns
2. Insert that number of rows into the Excel Spreadsheet at the current location
3. Paste the clipboard to the excel sheet


Thanks for the Help!


Fred

 

[Blade Hunter]

I just PM'd you but further to my reply over PM, Here are some options for getting the number of rows:

BEFORE COPY

Sub TransposingStuff()
Dim MyArr As Variant, NumRows As Long
MyArr = Application.Transpose(Selection)
NumRows = (UBound(MyArr))
MsgBox NumRows
End Sub

Sub CountingRows()
Dim NumRows As Long
NumRows = Selection.Rows.Count
MsgBox NumRows
End Sub

You can also do post copy but pre selection change.

As much as the rows.count method is great for this purpose, if you wanted to analyse the data in those cells (which in your case you do not) you would then go with the transpose method as you are actually populating an array (Called MyArr) with the data from every cell in the selection.

They both run as quick as each other, the Array version would use slightly more memory but it would be negligable, it's really up to you which one to use .

Cheers

Dan

 

[Fred916]

Dan,

That was exactly what I was looking for.

Thank you for the help!

Fred

 

[Blade Hunter]

Happy to help mate .

Cheers

Dan