Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell range copy
So heres what I'm Try to do.
Copy a range1 from sheet1 to sheet2 to first blank row Col "A". The range1 from sheet1 is of variable size and may contain empty cells. Also range1 may not be in the same place everytime on sheet1, but I have the current cell address which is the first column and last row of the range. curcell = ActiveWindow.RangeSelection.Address Maybe using .Resize to determine range size?? Thanks Allan |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell range copy
Hi Allan,
You can use things like: Cells(ActiveCell.Row, ActiveCell.Column).End(xlUp).Select to move up the column, but this will only take you from the current cell up to the first cell below a blank cell. So if your range does contain a blank above the curcell then you will need to use the above code again to jump the blanks to the next non-empty cell, and again to move to the top of the next block, and so on. The same methods can be used to move right across your range, but once again you will run into problems using this method when blanks exist. If you know more about your range then you might be able to do it, for example, if you know that the range always starts on row 1, or if you know there will be no blank cells in the first row of data, or if you know that the number of columns will always be consistent. If you don't mind having user input then you can ask for the range to be selected manually using something like: Dim myRange As Range On Error Resume Next Set myRange = Application.InputBox("Select Range", "Range", Type:=8) If Err.Number < 0 Then 'INPUT BOX WAS CANCELLED On Error GoTo 0 Else 'RANGE SELECTED On Error GoTo 0 myRange.Select End If I hope this helps, Sean. -- (please remember to click yes if replies you receive are helpful to you) "Allan" wrote: So heres what I'm Try to do. Copy a range1 from sheet1 to sheet2 to first blank row Col "A". The range1 from sheet1 is of variable size and may contain empty cells. Also range1 may not be in the same place everytime on sheet1, but I have the current cell address which is the first column and last row of the range. curcell = ActiveWindow.RangeSelection.Address Maybe using .Resize to determine range size?? Thanks Allan |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell range copy
maybe?
With Worksheets("Sheet2") LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row Activecell.CurrentRegion.Copy .Cells(LastRow, "A") End With -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Allan" wrote in message ... So heres what I'm Try to do. Copy a range1 from sheet1 to sheet2 to first blank row Col "A". The range1 from sheet1 is of variable size and may contain empty cells. Also range1 may not be in the same place everytime on sheet1, but I have the current cell address which is the first column and last row of the range. curcell = ActiveWindow.RangeSelection.Address Maybe using .Resize to determine range size?? Thanks Allan |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Activecell range copy
After putzing around for a while this is what I come up with.
This works because the active cell is in the CurrentRegion. Dim curcell As Variant Dim lastrow As Long lastrow = Sheets("sheet2").Range("A" & Rows.Count).End(xlUp).Row If lastrow < 1 Then lastrow = lastrow + 1 curcell = ActiveCell.Address ' anywhere in the current region Selection.Resize(Selection.Rows.Count, Selection.Columns.Count).Select Range(curcell).CurrentRegion.Copy Sheets("sheet2").Cells(lastrow, 1) End Sub -- AH "Allan" wrote: So heres what I'm Try to do. Copy a range1 from sheet1 to sheet2 to first blank row Col "A". The range1 from sheet1 is of variable size and may contain empty cells. Also range1 may not be in the same place everytime on sheet1, but I have the current cell address which is the first column and last row of the range. curcell = ActiveWindow.RangeSelection.Address Maybe using .Resize to determine range size?? Thanks Allan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
indicate activecell range | Excel Programming | |||
Copy File from activecell | Excel Discussion (Misc queries) | |||
Copy format of row above my activecell | Excel Programming | |||
ActiveCell.Copy Range("R3C27") | New Users to Excel | |||
Copy named range contents to activecell position | Excel Programming |