More efficient code wanted

  • Thread starter Thread starter fzl2007
  • Start date Start date
F

fzl2007

How do I write more condense code for the following?
I appreciate it.

.Cells(iRow + 1, "A").Value = ""
.Cells(iRow + 2, "A").Value = ""
.Cells(iRow + 3, "A").Value = ""
.Cells(iRow + 4, "A").Value = ""
.Cells(iRow + 5, "A").Value = ""
.Cells(iRow + 6, "A").Value = ""
.Cells(iRow + 7, "A").Value = ""
.Cells(iRow + 8, "A").Value = ""
.Cells(iRow + 9, "A").Value = ""
.Cells(iRow + 10, "A").Value = ""
.Cells(iRow + 11, "A").Value = ""
.Cells(iRow + 12, "A").Value = ""
.Cells(iRow + 13, "A").Value = ""
.Cells(iRow + 14, "A").Value = ""

.Cells(iRow + 1, "B").Value = ""
.Cells(iRow + 2, "B").Value = ""
.Cells(iRow + 3, "B").Value = ""
.Cells(iRow + 4, "B").Value = ""
.Cells(iRow + 5, "B").Value = ""
.Cells(iRow + 6, "B").Value = ""
.Cells(iRow + 7, "B").Value = ""
.Cells(iRow + 8, "B").Value = ""
.Cells(iRow + 9, "B").Value = ""
.Cells(iRow + 10, "B").Value = ""
.Cells(iRow + 11, "B").Value = ""
.Cells(iRow + 12, "B").Value = ""
.Cells(iRow + 13, "B").Value = ""
.Cells(iRow + 14, "B").Value = ""

.Cells(iRow + 1, "G").Value = ""
.Cells(iRow + 2, "G").Value = ""
.Cells(iRow + 3, "G").Value = ""
.Cells(iRow + 4, "G").Value = ""
.Cells(iRow + 5, "G").Value = ""
.Cells(iRow + 6, "G").Value = ""
.Cells(iRow + 7, "G").Value = ""
.Cells(iRow + 8, "G").Value = ""
.Cells(iRow + 9, "G").Value = ""
.Cells(iRow + 10, "G").Value = ""
.Cells(iRow + 11, "G").Value = ""
.Cells(iRow + 12, "G").Value = ""
.Cells(iRow + 13, "G").Value = ""
.Cells(iRow + 14, "G").Value = ""


Faye Larson
 
Try using this single statement in place of all those statements you
posted...

Range("A1:B14,G1:G14").Offset(iRow).Value = ""
 
.Range(.Cells(iRow + 1, "A"), .Cells(iRow + 14,
"B")).ClearContents
.Range(.Cells(iRow + 1, "G"), .Cells(iRow + 14,
"G")).ClearContents
 
Faye,
One way...

..Range(.Cells(iRow + 1, 1), .Cells(iRow + 14, 2)).ClearContents
..Range(.Cells(iRow + 1, 7), .Cells(iRow + 14, 7)).ClearContents
--
Jim Cone
Portland, Oregon USA
(Special Sort... http://www.contextures.com/excel-sort-addin.html)




"fzl2007" <[email protected]>
wrote in message How do I write more condense code for the following?
I appreciate it.
.Cells(iRow + 1, "A").Value = ""
.Cells(iRow + 2, "A").Value = ""
.Cells(iRow + 3, "A").Value = ""
.Cells(iRow + 4, "A").Value = ""
.Cells(iRow + 5, "A").Value = ""
.Cells(iRow + 6, "A").Value = ""
.Cells(iRow + 7, "A").Value = ""
.Cells(iRow + 8, "A").Value = ""
.Cells(iRow + 9, "A").Value = ""
.Cells(iRow + 10, "A").Value = ""
.Cells(iRow + 11, "A").Value = ""
.Cells(iRow + 12, "A").Value = ""
.Cells(iRow + 13, "A").Value = ""
.Cells(iRow + 14, "A").Value = ""

.Cells(iRow + 1, "B").Value = ""
.Cells(iRow + 2, "B").Value = ""
.Cells(iRow + 3, "B").Value = ""
.Cells(iRow + 4, "B").Value = ""
.Cells(iRow + 5, "B").Value = ""
.Cells(iRow + 6, "B").Value = ""
.Cells(iRow + 7, "B").Value = ""
.Cells(iRow + 8, "B").Value = ""
.Cells(iRow + 9, "B").Value = ""
.Cells(iRow + 10, "B").Value = ""
.Cells(iRow + 11, "B").Value = ""
.Cells(iRow + 12, "B").Value = ""
.Cells(iRow + 13, "B").Value = ""
.Cells(iRow + 14, "B").Value = ""

.Cells(iRow + 1, "G").Value = ""
.Cells(iRow + 2, "G").Value = ""
.Cells(iRow + 3, "G").Value = ""
.Cells(iRow + 4, "G").Value = ""
.Cells(iRow + 5, "G").Value = ""
.Cells(iRow + 6, "G").Value = ""
.Cells(iRow + 7, "G").Value = ""
.Cells(iRow + 8, "G").Value = ""
.Cells(iRow + 9, "G").Value = ""
.Cells(iRow + 10, "G").Value = ""
.Cells(iRow + 11, "G").Value = ""
.Cells(iRow + 12, "G").Value = ""
.Cells(iRow + 13, "G").Value = ""
.Cells(iRow + 14, "G").Value = ""
Faye Larson
 
sub blankem()
range("a1:b1,g1").resize(14)=""
'OR
range("a1:b1,g1").resize(14).clearcontents
end sub
 
That statement should have a "dot" in front of it so it references the same
worksheet object that your Cells statements reference...

..Range("A1:B14,G1:G14").Offset(iRow).Value = ""
 
Oh, and as others have shown, you can use the ClearContents property rather
than assigning the empty string...

..Range("A1:B14,G1:G14").Offset(iRow).ClearContents
 
Without the rest of the code (specifically what you are doing with iRow) it
is hard to say but:
For i = 1 to 14
..Cells(iRow + i, "A").Value = ""
..Cells(iRow + i, "B").Value = ""
..Cells(iRow + i, "G").Value = ""
Next
 
Back
Top