How to copy only to cells where there is data

[childofthe1980s]

Hello:

I have created a simple macro that, among other things, places a formula in
I2 that subtracts H2 from G2. The macro, then, copies this formula down the
entire column of I and formats column I to be "values" instead of "formulas".

That's all well and good. But I only want the macro to conduct this sort of
copying and formatting of column I only where there is data in column H. I
mean, if the data in the spreadsheet does not go past row 131, then I do not
want any data in column I that is beyond I131.

Could someone please review my macro below and let me know how I can modify
the VBA code to accomplish this? Thanks!

childofthe1980s

Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2").Select
Selection.Copy
Columns("I:I").Select
ActiveSheet.Paste
Range("I1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Need"
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

 

[Mike H]

Hi,

Try this

Range("I1").Value = "Need"
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2:I" & lastrow).Copy
Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

 

[Mike H]

Better

Sub nn()
Range("I1").Value = "Need"
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
With Range("I2:I" & lastrow)
.FormulaR1C1 = "=RC[-2]-RC[-1]"
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Hi,

Try this

Range("I1").Value = "Need"
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2:I" & lastrow).Copy
Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Hello:

I have created a simple macro that, among other things, places a formula in
I2 that subtracts H2 from G2. The macro, then, copies this formula down the
entire column of I and formats column I to be "values" instead of "formulas".

That's all well and good. But I only want the macro to conduct this sort of
copying and formatting of column I only where there is data in column H. I
mean, if the data in the spreadsheet does not go past row 131, then I do not
want any data in column I that is beyond I131.

Could someone please review my macro below and let me know how I can modify
the VBA code to accomplish this? Thanks!

childofthe1980s

Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2").Select
Selection.Copy
Columns("I:I").Select
ActiveSheet.Paste
Range("I1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Need"
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

 

[childofthe1980s]

This worked perfectly, Mike! Thank you!

If I have this issue in the future, what are some good VBA "buzzwords" to
put into my programming to tell it to only copy data to the last record where
there is data?

childofthe1980s

Better

Sub nn()
Range("I1").Value = "Need"
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
With Range("I2:I" & lastrow)
.FormulaR1C1 = "=RC[-2]-RC[-1]"
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Hi,

Try this

Range("I1").Value = "Need"
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2:I" & lastrow).Copy
Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Hello:

I have created a simple macro that, among other things, places a formula in
I2 that subtracts H2 from G2. The macro, then, copies this formula down the
entire column of I and formats column I to be "values" instead of "formulas".

That's all well and good. But I only want the macro to conduct this sort of
copying and formatting of column I only where there is data in column H. I
mean, if the data in the spreadsheet does not go past row 131, then I do not
want any data in column I that is beyond I131.

Could someone please review my macro below and let me know how I can modify
the VBA code to accomplish this? Thanks!

childofthe1980s

Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2").Select
Selection.Copy
Columns("I:I").Select
ActiveSheet.Paste
Range("I1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Need"
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False

 

[Mike H]

Hi,

It's a vey common question and there are several way of finding the last row
including the one I used

lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
Have a lookhere for som other methods, it also tells you seme methods for
use on a worksheet

http://www.mvps.org/dmcritchie/excel/lastcell.htm
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

This worked perfectly, Mike! Thank you!

If I have this issue in the future, what are some good VBA "buzzwords" to
put into my programming to tell it to only copy data to the last record where
there is data?

childofthe1980s

Better

Sub nn()
Range("I1").Value = "Need"
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
With Range("I2:I" & lastrow)
.FormulaR1C1 = "=RC[-2]-RC[-1]"
.Copy
.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With
Application.CutCopyMode = False
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.

Hi,

Try this

Range("I1").Value = "Need"
lastrow = Cells(Cells.Rows.Count, "H").End(xlUp).Row
Range("I2:I" & lastrow).FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2:I" & lastrow).Copy
Range("I2:I" & lastrow).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False


--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


:

Hello:

I have created a simple macro that, among other things, places a formula in
I2 that subtracts H2 from G2. The macro, then, copies this formula down the
entire column of I and formats column I to be "values" instead of "formulas".

That's all well and good. But I only want the macro to conduct this sort of
copying and formatting of column I only where there is data in column H. I
mean, if the data in the spreadsheet does not go past row 131, then I do not
want any data in column I that is beyond I131.

Could someone please review my macro below and let me know how I can modify
the VBA code to accomplish this? Thanks!

childofthe1980s

Range("I2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("I2").Select
Selection.Copy
Columns("I:I").Select
ActiveSheet.Paste
Range("I1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "Need"
Columns("I:I").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False