copy a selection into a variable

[Janis]

I recorded this macro and it works. I need to delete the top line on a
spreadsheet but the report date I need to save as a variable. I tried copy
and paste but you can't paste the copy value into the variable. So I can't
figure out how to get the copy selection into the variable.
tnx,



Private Sub deleteDateRow1()
'
Dim FrReptDate As Date


'
' Keyboard Shortcut: Option+Cmd+z
'

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlTop
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("A1").Select
Selection.Copy
FrReptDate = Selection.Paste
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp

End Sub

 

[JE McGimpsey]

I recorded this macro and it works. I need to delete the top line on a
spreadsheet but the report date I need to save as a variable. I tried copy
and paste but you can't paste the copy value into the variable. So I can't
figure out how to get the copy selection into the variable.

Note sure exactly what your selection is supposed to be when the macro
is invoked, but perhaps this is close:

Private Sub deleteDateRow1()
Dim FrReptDate As Date
With Range("A1")
FrReptDate = .Value
.EntireRow.Delete Shift:=xlUp
End With
End Sub

While the Macro Recorder always records selections, good coding practice
almost always eschews selections. Addressing the Range Objects directly
is much more efficient and easier to maintain.

 

[Janis]

Thanks for the correct syntax, what it is doing is removing a blank line with
the report date just before the field names so I can use the rest of the
spreadsheet for a database. Then after I manipulate the data I can put the
report header back on.

 

[Janis]

I changed it a little to include the fr report date and now it doesn't work.

Private Sub deleteDateRow1()
'
' deleteDateRow1 Macro
Dim FrReptDate As Date
Dim ToReptDate As Date
Dim rng As Range
Set rng = Range("A1", "B1")
With rng
FrReptDate = Cells.A1.Value
ToReptDate = Cells.B1.Value
.EntireRow.Delete Shift:=xlUp

Debug.Print FrReptDate
End With
End Sub

 

[JE McGimpsey]

Thanks for the correct syntax, what it is doing is removing a blank line with
the report date just before the field names so I can use the rest of the
spreadsheet for a database. Then after I manipulate the data I can put the
report header back on.

I'm probably being obtuse, and I'm sure the statement above makes
perfect sense to you, but I'm afraid it's incomprehensible to me. Are
you saying that the macro "is doing" what you state, or that it "should
do" that?

How can a "line" be both blank and "with the report date". What does
"just before the field names" mean - i.e., the row above? the column to
the left?

If you still need assistance, perhaps you can use your actual cell
references and be a bit more explicit about the steps you're looking for.

 

[JE McGimpsey]

I changed it a little to include the fr report date and now it doesn't work.

Private Sub deleteDateRow1()
'
' deleteDateRow1 Macro
Dim FrReptDate As Date
Dim ToReptDate As Date
Dim rng As Range
Set rng = Range("A1", "B1")
With rng
FrReptDate = Cells.A1.Value
ToReptDate = Cells.B1.Value
.EntireRow.Delete Shift:=xlUp

Debug.Print FrReptDate
End With
End Sub

"A1" and "B1" aren't child objects of the Cells object, which

Cells.A1.Value

implies. I suspect you're trying to do something like:

Private Sub deleteDateRow1()
Dim FrReptDate As Date
Dim ToReptDate As Date
With Range("A1")
FrReptDate = .Value
ToReptDate = .Offset(0, 1).Value
.EntireRow.Delete Shift:=xlUp
End With
Debug.Print FrReptDate, ToReptDate
End Sub

 

[Janis]

JE
thanks, that was it I didn't know how to do the cell offset.
thanks,
janis