Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > More efficient code wanted
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 23
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 236
Default More efficient code wanted
.Range(.Cells(iRow + 1, "A"), .Cells(iRow + 14,
"B")).ClearContents
.Range(.Cells(iRow + 1, "G"), .Cells(iRow + 14,
"G")).ClearContents
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,549
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,522
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,934
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help wanted with Code, Please DaveM[_2_] Excel Programming 6 July 24th 07 03:24 PM
More efficient code Bob Excel Programming 3 June 7th 07 09:44 PM
Help wanted with VBA code [email protected] Excel Programming 4 September 12th 06 09:44 PM
Efficient Code GregR Excel Programming 7 June 27th 05 04:09 PM
vba code wanted helmekki[_2_] Excel Programming 2 May 12th 04 11:05 PM


All times are GMT +1. The time now is 12:23 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement