how to find first blank cell in column then copy all preceding cells

Hi all,

I am working with arrays that extend far beyond their actual content, and so i am looking for a way, through macros, to find the first blank cell in a column and then copy all preceding cells in that column. Thank you!

So the only thing is there are multiple columns in this array and so finding the first blank in the whole array won't necessarily mean finding the first blank in the column that I want, unless I interpreted the code wrongly. Or do you mean that I should replace "A' with index of whichever column I want?

Is there any way to do the same thing if I don't have the column letter but only have the column number?

Thanks! I was also having some problems with the following code:

Range("A2:CN750").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet8").Select
Range("A2:CN750").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

This is giving me errors when I try to paste, and it suggests I either select a single cell or the range of the same size, but it doesn't seem to be working for me

THank you! I am also trying to search for an instance of a specific word in an arrray but my code does not seem to work.

'first i get the text i want to search for from another sheet
Sheets("AdjustBaseEnv").Select
Range("C16").Select
Selection.Copy

'then i paste it on to the sheet i want to look for so I can use it in the find function
Sheets("Sheet8").Select
Range("AJ2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Dim selectedcurv As String

'then i save it as a stirng in the code so i can refer to it in the find function
selectedcurve = Range("AJ2").Value

'then i select a large range to search for it
Range("A1:AN200").Select
Cells.Find(What:=selectedcurv, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=True).Activate

I don't know exactly what I have done wrong but for some reason it just selects a cell in the second column that has no instance of the word!!