PDA

View Full Version : [SOLVED] Copy range to blank cell



ArenA777
08-10-2017, 09:21 AM
I want Vba to copy the column of data that is found under the name "Value" until it reaches a blank space and then copy that data in another worksheet. this is what I have so far.


Sub select_cellsCounty()
Set cell = Cells.Find(What:="Value")
cell.Select
Set Start= ActiveCell.Offset(1, 0).Select
Set ans = Application.InputBox(Start, Start, Type:=8)

Range(ans, Cells(Rows.Count, ans.Column).End(xlUp)).Select

Selection.Copy
Sheets("State").Select
Range("A2:A").Select
ActiveSheet.Paste
End Sub

I want to get rid of the input box and have it do it automatically, but I dont know how to set the beginning of the range as the cell one below the name "Value". I'm using Excel 2013.


Thanks,

Bob Phillips
08-10-2017, 10:28 AM
What is the Inputbox for, you are finding the column.

ArenA777
08-10-2017, 11:01 AM
The inputbox was used initially to select the first cell in the range, but i want to get rid of that.
I want something like this

Range(cells.Find(What:="Value").Offset(1, 0), cells(Rows.Count, ans.Column).End(xlUp)).Select

Where the cell below "Value" is the first cell in the range, and the range is selected until the first empty cell. I am trying this but it doesnt allow me to do it.

SamT
08-10-2017, 12:16 PM
Dim Found As Range
Set Found = Rows(1).Find(What:="Value").Offset(1, 0)
Range(Found, Cells(Rows.Count, Found.Column).End(xlUp)).Copy
Possible Alternate, If you can guarantee that the column contains no empty cells above the bottom most used cell

Range(Found, Found.End(xlDown)).Copy

ArenA777
08-10-2017, 12:48 PM
Dim Found As Range
Set Found = Rows(1).Find(What:="Value").Offset(1, 0)
Range(Found, Cells(Rows.Count, Found.Column).End(xlUp)).Copy
Possible Alternate, If you can guarantee that the column contains no empty cells above the bottom most used cell

Range(Found, Found.End(xlDown)).Copy


I tried what you said, but I keep getting an "object variable or With block variable not set" error.

Is there anyway to avoid the error

mdmackillop
08-10-2017, 01:54 PM
Sub Test()
Dim Found As Range
Set Found = Cells.Find(What:="Value").Offset(1, 0)
Range(Found, Found.End(xlDown)).Copy Sheets("State").Range("A2")
End Sub

ArenA777
08-10-2017, 02:14 PM
Thank You, that worked perfectly

mdmackillop
08-10-2017, 02:22 PM
Happy to help. For the future, please use meaningful titles rather than "Please Help" and the like. I'll change this title.

SamT
08-10-2017, 03:17 PM
JSMH

Paul_Hossler
08-10-2017, 04:22 PM
JSMH

?

mdmackillop
08-11-2017, 01:35 AM
Didn't know either! (http://onlineslangdictionary.com/meaning-definition-of/jsmh)