Dynamic Range Syntax 2

[sterlecki]

I'm having trouble with the syntax when naming a range for pasting the value of a single cell into the same column but down to the last row of data.

So if Activecell.address is $D$2
X = Activecell.column (ie 4)
Y = activecell.row (ie 2)
r = ActiveSheet.UsedRange.Rows.Count (say it's row 5)

I want to copy the active cell and paste it in the same column down to the last used row r.

If it was hardcoded it would be:

Range("D2").Select
Selection.Copy
Range("D25").Select
ActiveSheet.Paste

[highlight] What's wrong here?

Range((x & y) & ":" & (x , r)).Select
ActiveSheet.Paste
[/highlight]

 

[SkipVought]

hi,

I want to copy the active cell and paste it in the same column down to the last used row r.

  With ActiveCell
    .Copy
    Range(.Cells(1, 1), .Cells(1, 1).End(xlDown)).PasteSpecial xlPasteAll
  End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sterlecki]

Skip,

I'm closer than I was but it is pasting all the way to the last available row in the worksheet.

Obviously the usedrange property has it's limitations if the worksheet has been previously altered.

How can I limit the pasting to only the rows with data in the adjacent column to the left?

 

[SkipVought]

I assumed that there was data in every cell of the used range of the selected column. Obviously NOT!

Try this instead

  with ActiveCell
    .Copy
    with usedrange
       Range(activecell, .Cells(.row+.rows.count-1,activecell.column)).PasteSpecial xlPasteAll
    end with
  End With

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sterlecki]

Skip,

I get an error at the highlighted line. "Object Variable or With block variable not set.

Sub SetColumnWidth15()
'
' SetColumnWidth15 Macro
'

'
    Dim strRecordB As String
    Dim r As Integer
    Dim i As Integer
    Dim y As Integer
    Dim x As Integer
    Dim c As Integer
    Dim UsedRange as Range

    i = 0
    
    strRecordB = "B00001000Annotation Polygon"
    
    Columns("A:Z").Select
    Selection.ColumnWidth = 15
    With Selection
        .HorizontalAlignment = xlRight
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("B2").Select
    r = Range("B2").End(xlDown).Row
    
    Columns("A:A").Select
    Selection.ColumnWidth = 2
    Range("A1") = strRecordB
    r = ActiveSheet.UsedRange.Rows.Count
    Range("A2") = "CD"
    Range("a2").Select
    Selection.AutoFill Destination:=Range("a2:a" & r)
    Range("B2").Select
    
Do Until i = 6
    ActiveCell.Offset(0, 2).Activate
    Selection.EntireColumn.Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.ColumnWidth = 1
    ActiveCell.Offset(1, 0).Activate
    ActiveCell = "D"
    Range(ActiveCell.Address).Select
    Selection.Copy
    c = ActiveSheet.UsedRange.Columns.Count
    x = ActiveCell.Column
    y = ActiveCell.Row
    
    With ActiveCell  'paste active cell value in column
    .Copy
    
        With UsedRange
   [highlight]         Range(ActiveCell, .Cells(.Row + .Rows.Count - 1, ActiveCell.Column)).PasteSpecial xlPasteAll
[/highlight]
         End With
    
  End With
    
    
    ActiveCell.Offset(0, 1).Activate
    i = i + 1
    Loop

End Sub

 

[SkipVought]

I sure don't know why. I copied this AS IS and ran it on my sheet sucessfully.

Sub testit()
    With ActiveCell  'paste active cell value in column
    .Copy
    
        With UsedRange
            Range(ActiveCell, .Cells(.Row + .Rows.Count - 1, ActiveCell.Column)).PasteSpecial xlPasteAll

         End With
    
  End With
End Sub

Skip,
[sub]
[glasses]Just traded in my [b]old subtlety[/b]...
for a [b]NUANCE![/b][tongue][/sub]

 

[sterlecki]

When I copied your last entry AS IS into a new worksheet. I placed data in column A rows 2 to 10 and data in B2 making this the active cell.

When I ran the macro it said compile error: Variable not defined and highlighted UsedRange

If I placed a statement:

Dim UsedRange as range

I get the error mentioned in my previous entry.

could there be a difference as to how our differnet versions of Excel are configured?

Thanks for you help on this. It's been confuonding me for two days.

 

[PHV]

With [!]ActiveSheet.[/!]UsedRange

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[sterlecki]

That was it PHV

Sub testit()
    With ActiveCell  'paste active cell value in column
    .Copy
    
        With [highlight]ActiveSheet.[/highlight]UsedRange
            Range(ActiveCell, .Cells(.Row + .Rows.Count - 1, ActiveCell.Column)).PasteSpecial xlPasteAll

         End With
    
  End With
End Sub

Works like a charm. Details, details, Details

 

[sterlecki]

Skip and PHV

Assigned you both stars but forgot to include a Big Thanks!
Entire code now runs perfectly.

Don't know how we'd all survive without your help.