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.
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.
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)
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.