ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   More efficient code wanted (https://www.excelbanter.com/excel-programming/441070-more-efficient-code-wanted.html)

fzl2007
More efficient code wanted
 
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

Rick Rothstein
More efficient code wanted
 
Try using this single statement in place of all those statements you
posted...

Range("A1:B14,G1:G14").Offset(iRow).Value = ""

--
Rick (MVP - Excel)



"fzl2007" 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


AB[_2_]
More efficient code wanted
 
.Range(.Cells(iRow + 1, "A"), .Cells(iRow + 14,
"B")).ClearContents
.Range(.Cells(iRow + 1, "G"), .Cells(iRow + 14,
"G")).ClearContents

Jim Cone[_2_]
More efficient code wanted
 
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"
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

Don Guillett[_2_]
More efficient code wanted
 
sub blankem()
range("a1:b1,g1").resize(14)=""
'OR
range("a1:b1,g1").resize(14).clearcontents
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fzl2007" 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


Rick Rothstein
More efficient code wanted
 
You missed the iRow part.

--
Rick (MVP - Excel)



"Don Guillett" wrote in message
...
sub blankem()
range("a1:b1,g1").resize(14)=""
'OR
range("a1:b1,g1").resize(14).clearcontents
end sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"fzl2007" 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

Rick Rothstein
More efficient code wanted
 
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 = ""

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Try using this single statement in place of all those statements you
posted...

Range("A1:B14,G1:G14").Offset(iRow).Value = ""

--
Rick (MVP - Excel)



"fzl2007" 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

Rick Rothstein
More efficient code wanted
 
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).ClearContent s

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
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 = ""

--
Rick (MVP - Excel)



"Rick Rothstein" wrote in message
...
Try using this single statement in place of all those statements you
posted...

Range("A1:B14,G1:G14").Offset(iRow).Value = ""

--
Rick (MVP - Excel)



"fzl2007" 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


All times are GMT +1. The time now is 03:26 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com