Range Question

  • Thread starter Thread starter slappy54
  • Start date Start date
S

slappy54

I would like to be able to grab the row information from the first Do-While
loop (column D) and use it to create a range for the ActiveCell.Copy and
PasteSpecial (column J), instead of using a loop to find once again an empty
value. This works, but I couldn't figure out the code for the range to make
it run faster. Any help is greatly appreciated.

Thanks,
Mike

Range("D2").Select
Do While ActiveCell.Value <> Empty
ActiveCell.Offset(1, 0).Select
Loop
For I = 1 To 15
ActiveCell.Offset(1, 0).EntireRow.Delete
Next I
Range("J2").Select
Do While ActiveCell.Offset(1, -1).Value <> Empty
ActiveCell.Copy
ActiveCell.Offset(1, O).PasteSpecial (xlPasteFormulas)
Loop
Range("J2").Select
 
The way to get the row of the active cell is:

rw = activecell.row

rw is a variable that stores the row number.
You don't have to loop to get to the first empty cell
down. Assuming your current celection is a single cell
somewhere in the target column and above the empty cell
looked for, all you need to do is:

selection.end(xldown).offset(1,0).select

Alternatively, if you want to select a range expanding
from your currently selected cell (single) to the last one
down before an empty cell, you can use:

range(selection, selection.end(xldown)).select

Likewise with xlup, xltoright, xltoleft.

Note: if your current selection is a range rather than a
single cell, it will still work but select a wider range
matching your original selection. Actually, this is very
useful in deleting/inserting entire rows or columns. If
you select an endtire row and then use the last expression
above you will select all the rows from the currently
selected one to the last one before a blank cell in the
column where your activecell is.

Hope this helps,

Nikos Y.
 
Back
Top