Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Macro's
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 788
Default Macro's
I am making.. or trying to make a macro to clear data from fields on my
sheet. I keep on getting different error messages and nothing I have found so
far ( going on 60.00 worth of books) have help me find that anwser.. I am
going to take a class... i am in more urgent needs of anwsers. do.. you want
me to post the code?
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Macro's
On 21 Apr, 08:02, Chris wrote:
I am making.. or trying to make a macro to clear data from fields on my
sheet. I keep on getting different error messages and nothing I have found so
far ( going on 60.00 worth of books) have help me find that anwser.. I am
going to take a class... i am in more urgent needs of anwsers. do.. you want
me to post the code?



Chris,

Use the Clearcontents command:

Range("First cells ref:Second cell ref").clearcontents

e.g. Range("A1:B20").clearcontents

where the range is the range that you want to clear.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Macro's
Hi Briziers,

You should post the relevant code and indicate both the
line that is highlighted when the code errors, and the error
message whiich you encounter.


---
Regards,
Norman




"Brizers" wrote in message
oups.com...
On 21 Apr, 08:02, Chris wrote:
I am making.. or trying to make a macro to clear data from fields on
my
sheet. I keep on getting different error messages and nothing I have
found so
far ( going on 60.00 worth of books) have help me find that anwser.. I am
going to take a class... i am in more urgent needs of anwsers. do.. you
want
me to post the code?



Chris,

Use the Clearcontents command:

Range("First cells ref:Second cell ref").clearcontents

e.g. Range("A1:B20").clearcontents

where the range is the range that you want to clear.



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3
Default Macro's
Apologies Norman, it was Chris who asked the question (I suggested an
answer). For reference, this was:

Range("First cell reference:Second cell reference").Clearcontents

e.g. Range("A1:B20").Clearcontents


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Macro's
Hi Briers,

Sorry! My post was intended as a reponse to Chris but I
inadvertently appended it to the wrong strand.


---
Regards,
Norman


"Brizers" wrote in message
oups.com...
Apologies Norman, it was Chris who asked the question (I suggested an
answer). For reference, this was:

Range("First cell reference:Second cell reference").Clearcontents

e.g. Range("A1:B20").Clearcontents


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 788
Default Macro's

there you go... have at it...


Private Sub CommandButton1_Click()
Sub Clear()
'
' Clear Macro
' Macro recorded 4/17/2007 by marrech
'

'
Range("F18:F19").Select
ActiveCell.FormulaR1C1 = ""
Range("G18:G19").Select
ActiveCell.FormulaR1C1 = ""
Range("H18:H19").Select
ActiveCell.FormulaR1C1 = ""
Range("I18:I19").Select
ActiveCell.FormulaR1C1 = ""
Range("J18:J19").Select
ActiveCell.FormulaR1C1 = ""
Range("F27:F28").Select
ActiveCell.FormulaR1C1 = ""
Range("G27:G28").Select
ActiveCell.FormulaR1C1 = ""
Range("H27:H28").Select
ActiveCell.FormulaR1C1 = ""
Range("I27:I28").Select
ActiveCell.FormulaR1C1 = ""
Range("J27:J28").Select
ActiveCell.FormulaR1C1 = ""
Range("C16").Select
ActiveCell.FormulaR1C1 = ""
Range("C17").Select
ActiveCell.FormulaR1C1 = ""
Range("C18").Select
ActiveCell.FormulaR1C1 = ""
Range("C19").Select
ActiveCell.FormulaR1C1 = ""
Range("C20").Select
ActiveCell.FormulaR1C1 = ""
Range("C21").Select
ActiveCell.FormulaR1C1 = ""
Range("C22").Select
ActiveCell.FormulaR1C1 = ""
Range("C23").Select
ActiveCell.FormulaR1C1 = ""
Range("C24").Select
ActiveCell.FormulaR1C1 = ""
Range("C25").Select
ActiveCell.FormulaR1C1 = ""
Range("C26").Select
ActiveCell.FormulaR1C1 = ""
Range("C27").Select
ActiveCell.FormulaR1C1 = ""
Range("C28").Select
ActiveCell.FormulaR1C1 = ""
Range("C29").Select
ActiveCell.FormulaR1C1 = ""
Range("C30").Select
ActiveCell.FormulaR1C1 = ""
Range("D32").Select
ActiveCell.FormulaR1C1 = ""
Range("D33").Select
ActiveCell.FormulaR1C1 = ""
Range("D34").Select
ActiveCell.FormulaR1C1 = ""
Range("H32").Select
ActiveCell.FormulaR1C1 = ""
Range("I32").Select
ActiveCell.FormulaR1C1 = ""
Range("J32").Select
ActiveCell.FormulaR1C1 = ""
Range("K32").Select
ActiveCell.FormulaR1C1 = ""
Range("L32").Select
ActiveCell.FormulaR1C1 = ""
Range("L33").Select
ActiveCell.FormulaR1C1 = ""
Range("K33").Select
ActiveCell.FormulaR1C1 = ""
Range("J33").Select
ActiveCell.FormulaR1C1 = ""
Range("I33").Select
ActiveCell.FormulaR1C1 = ""
Range("H33").Select
ActiveCell.FormulaR1C1 = ""
Range("H34").Select
ActiveCell.FormulaR1C1 = ""
Range("I34").Select
ActiveCell.FormulaR1C1 = ""
Range("J34").Select
ActiveCell.FormulaR1C1 = ""
Range("K34").Select
ActiveCell.FormulaR1C1 = ""
Range("L34").Select
ActiveCell.FormulaR1C1 = ""
Range("L35").Select
ActiveCell.FormulaR1C1 = ""
Range("K35").Select
ActiveCell.FormulaR1C1 = ""
Range("J35").Select
ActiveCell.FormulaR1C1 = ""
Range("I35").Select
ActiveCell.FormulaR1C1 = ""
Range("H35").Select
ActiveCell.FormulaR1C1 = ""
Range("H38").Select
End Sub
End Sub
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Macro's
Hi Chris,

Your 98 lines of code can be condensed to a
single instruction

Range("F18:J19, F27:J28, C16:C30, D32:D34, H32:L35"). _
ClearContents

Notr that your code had two headers:

Private Sub CommandButton1_Click()
Sub Clear()

and two closing lines:

End Sub
End Sub

Additionally, it is not necessary to select a range in
order to clear its contents and it is not necessary to
clear the cells individually.


---
Regards,
Norman




"Chris" wrote in message
...

there you go... have at it...


Private Sub CommandButton1_Click()
Sub Clear()
'
' Clear Macro
' Macro recorded 4/17/2007 by marrech
'

'
Range("F18:F19").Select
ActiveCell.FormulaR1C1 = ""
Range("G18:G19").Select
ActiveCell.FormulaR1C1 = ""
Range("H18:H19").Select
ActiveCell.FormulaR1C1 = ""
Range("I18:I19").Select
ActiveCell.FormulaR1C1 = ""
Range("J18:J19").Select
ActiveCell.FormulaR1C1 = ""
Range("F27:F28").Select
ActiveCell.FormulaR1C1 = ""
Range("G27:G28").Select
ActiveCell.FormulaR1C1 = ""
Range("H27:H28").Select
ActiveCell.FormulaR1C1 = ""
Range("I27:I28").Select
ActiveCell.FormulaR1C1 = ""
Range("J27:J28").Select
ActiveCell.FormulaR1C1 = ""
Range("C16").Select
ActiveCell.FormulaR1C1 = ""
Range("C17").Select
ActiveCell.FormulaR1C1 = ""
Range("C18").Select
ActiveCell.FormulaR1C1 = ""
Range("C19").Select
ActiveCell.FormulaR1C1 = ""
Range("C20").Select
ActiveCell.FormulaR1C1 = ""
Range("C21").Select
ActiveCell.FormulaR1C1 = ""
Range("C22").Select
ActiveCell.FormulaR1C1 = ""
Range("C23").Select
ActiveCell.FormulaR1C1 = ""
Range("C24").Select
ActiveCell.FormulaR1C1 = ""
Range("C25").Select
ActiveCell.FormulaR1C1 = ""
Range("C26").Select
ActiveCell.FormulaR1C1 = ""
Range("C27").Select
ActiveCell.FormulaR1C1 = ""
Range("C28").Select
ActiveCell.FormulaR1C1 = ""
Range("C29").Select
ActiveCell.FormulaR1C1 = ""
Range("C30").Select
ActiveCell.FormulaR1C1 = ""
Range("D32").Select
ActiveCell.FormulaR1C1 = ""
Range("D33").Select
ActiveCell.FormulaR1C1 = ""
Range("D34").Select
ActiveCell.FormulaR1C1 = ""
Range("H32").Select
ActiveCell.FormulaR1C1 = ""
Range("I32").Select
ActiveCell.FormulaR1C1 = ""
Range("J32").Select
ActiveCell.FormulaR1C1 = ""
Range("K32").Select
ActiveCell.FormulaR1C1 = ""
Range("L32").Select
ActiveCell.FormulaR1C1 = ""
Range("L33").Select
ActiveCell.FormulaR1C1 = ""
Range("K33").Select
ActiveCell.FormulaR1C1 = ""
Range("J33").Select
ActiveCell.FormulaR1C1 = ""
Range("I33").Select
ActiveCell.FormulaR1C1 = ""
Range("H33").Select
ActiveCell.FormulaR1C1 = ""
Range("H34").Select
ActiveCell.FormulaR1C1 = ""
Range("I34").Select
ActiveCell.FormulaR1C1 = ""
Range("J34").Select
ActiveCell.FormulaR1C1 = ""
Range("K34").Select
ActiveCell.FormulaR1C1 = ""
Range("L34").Select
ActiveCell.FormulaR1C1 = ""
Range("L35").Select
ActiveCell.FormulaR1C1 = ""
Range("K35").Select
ActiveCell.FormulaR1C1 = ""
Range("J35").Select
ActiveCell.FormulaR1C1 = ""
Range("I35").Select
ActiveCell.FormulaR1C1 = ""
Range("H35").Select
ActiveCell.FormulaR1C1 = ""
Range("H38").Select
End Sub
End Sub


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 788
Default Macro's
okay so this is what I got...

Private Sub CommandButton1_Click()
Sub Clear()

Range("F18:J19, F27:J28, C16:C30, D32:D34, H32:L35"). _
ClearContents

End Sub
End Sub

but it's still giving me... Compile Error:

(expected end sub) and has this:
Private Sub CommandButton1_Click()

in yellow.


"Norman Jones" wrote:

Hi Chris,

Your 98 lines of code can be condensed to a
single instruction

Range("F18:J19, F27:J28, C16:C30, D32:D34, H32:L35"). _
ClearContents

Notr that your code had two headers:

Private Sub CommandButton1_Click()
Sub Clear()

and two closing lines:

End Sub
End Sub

Additionally, it is not necessary to select a range in
order to clear its contents and it is not necessary to
clear the cells individually.


---
Regards,
Norman




"Chris" wrote in message
...

there you go... have at it...


Private Sub CommandButton1_Click()
Sub Clear()
'
' Clear Macro
' Macro recorded 4/17/2007 by marrech
'

'
Range("F18:F19").Select
ActiveCell.FormulaR1C1 = ""
Range("G18:G19").Select
ActiveCell.FormulaR1C1 = ""
Range("H18:H19").Select
ActiveCell.FormulaR1C1 = ""
Range("I18:I19").Select
ActiveCell.FormulaR1C1 = ""
Range("J18:J19").Select
ActiveCell.FormulaR1C1 = ""
Range("F27:F28").Select
ActiveCell.FormulaR1C1 = ""
Range("G27:G28").Select
ActiveCell.FormulaR1C1 = ""
Range("H27:H28").Select
ActiveCell.FormulaR1C1 = ""
Range("I27:I28").Select
ActiveCell.FormulaR1C1 = ""
Range("J27:J28").Select
ActiveCell.FormulaR1C1 = ""
Range("C16").Select
ActiveCell.FormulaR1C1 = ""
Range("C17").Select
ActiveCell.FormulaR1C1 = ""
Range("C18").Select
ActiveCell.FormulaR1C1 = ""
Range("C19").Select
ActiveCell.FormulaR1C1 = ""
Range("C20").Select
ActiveCell.FormulaR1C1 = ""
Range("C21").Select
ActiveCell.FormulaR1C1 = ""
Range("C22").Select
ActiveCell.FormulaR1C1 = ""
Range("C23").Select
ActiveCell.FormulaR1C1 = ""
Range("C24").Select
ActiveCell.FormulaR1C1 = ""
Range("C25").Select
ActiveCell.FormulaR1C1 = ""
Range("C26").Select
ActiveCell.FormulaR1C1 = ""
Range("C27").Select
ActiveCell.FormulaR1C1 = ""
Range("C28").Select
ActiveCell.FormulaR1C1 = ""
Range("C29").Select
ActiveCell.FormulaR1C1 = ""
Range("C30").Select
ActiveCell.FormulaR1C1 = ""
Range("D32").Select
ActiveCell.FormulaR1C1 = ""
Range("D33").Select
ActiveCell.FormulaR1C1 = ""
Range("D34").Select
ActiveCell.FormulaR1C1 = ""
Range("H32").Select
ActiveCell.FormulaR1C1 = ""
Range("I32").Select
ActiveCell.FormulaR1C1 = ""
Range("J32").Select
ActiveCell.FormulaR1C1 = ""
Range("K32").Select
ActiveCell.FormulaR1C1 = ""
Range("L32").Select
ActiveCell.FormulaR1C1 = ""
Range("L33").Select
ActiveCell.FormulaR1C1 = ""
Range("K33").Select
ActiveCell.FormulaR1C1 = ""
Range("J33").Select
ActiveCell.FormulaR1C1 = ""
Range("I33").Select
ActiveCell.FormulaR1C1 = ""
Range("H33").Select
ActiveCell.FormulaR1C1 = ""
Range("H34").Select
ActiveCell.FormulaR1C1 = ""
Range("I34").Select
ActiveCell.FormulaR1C1 = ""
Range("J34").Select
ActiveCell.FormulaR1C1 = ""
Range("K34").Select
ActiveCell.FormulaR1C1 = ""
Range("L34").Select
ActiveCell.FormulaR1C1 = ""
Range("L35").Select
ActiveCell.FormulaR1C1 = ""
Range("K35").Select
ActiveCell.FormulaR1C1 = ""
Range("J35").Select
ActiveCell.FormulaR1C1 = ""
Range("I35").Select
ActiveCell.FormulaR1C1 = ""
Range("H35").Select
ActiveCell.FormulaR1C1 = ""
Range("H38").Select
End Sub
End Sub



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 5,302
Default Macro's
Hi Chris,

Di you see my comment:

Notr that your code had two headers:

Private Sub CommandButton1_Click()
Sub Clear()

and two closing lines:

End Sub
End Sub

Try:

'=============
Private Sub CommandButton1_Click()
Range("F18:J19, F27:J28, C16:C30, D32:D34, H32:L35"). _
ClearContents
End Sub
'<<=============


---
Regards,
Norman
Microsoft Excel MVP



"Chris" wrote in message
...
okay so this is what I got...

Private Sub CommandButton1_Click()
Sub Clear()

Range("F18:J19, F27:J28, C16:C30, D32:D34, H32:L35"). _
ClearContents

End Sub
End Sub

but it's still giving me... Compile Error:

(expected end sub) and has this:
Private Sub CommandButton1_Click()

in yellow.


"Norman Jones" wrote:

Hi Chris,

Your 98 lines of code can be condensed to a
single instruction

Range("F18:J19, F27:J28, C16:C30, D32:D34, H32:L35"). _
ClearContents

Notr that your code had two headers:

Private Sub CommandButton1_Click()
Sub Clear()

and two closing lines:

End Sub
End Sub

Additionally, it is not necessary to select a range in
order to clear its contents and it is not necessary to
clear the cells individually.


---
Regards,
Norman




"Chris" wrote in message
...

there you go... have at it...


Private Sub CommandButton1_Click()
Sub Clear()
'
' Clear Macro
' Macro recorded 4/17/2007 by marrech
'

'
Range("F18:F19").Select
ActiveCell.FormulaR1C1 = ""
Range("G18:G19").Select
ActiveCell.FormulaR1C1 = ""
Range("H18:H19").Select
ActiveCell.FormulaR1C1 = ""
Range("I18:I19").Select
ActiveCell.FormulaR1C1 = ""
Range("J18:J19").Select
ActiveCell.FormulaR1C1 = ""
Range("F27:F28").Select
ActiveCell.FormulaR1C1 = ""
Range("G27:G28").Select
ActiveCell.FormulaR1C1 = ""
Range("H27:H28").Select
ActiveCell.FormulaR1C1 = ""
Range("I27:I28").Select
ActiveCell.FormulaR1C1 = ""
Range("J27:J28").Select
ActiveCell.FormulaR1C1 = ""
Range("C16").Select
ActiveCell.FormulaR1C1 = ""
Range("C17").Select
ActiveCell.FormulaR1C1 = ""
Range("C18").Select
ActiveCell.FormulaR1C1 = ""
Range("C19").Select
ActiveCell.FormulaR1C1 = ""
Range("C20").Select
ActiveCell.FormulaR1C1 = ""
Range("C21").Select
ActiveCell.FormulaR1C1 = ""
Range("C22").Select
ActiveCell.FormulaR1C1 = ""
Range("C23").Select
ActiveCell.FormulaR1C1 = ""
Range("C24").Select
ActiveCell.FormulaR1C1 = ""
Range("C25").Select
ActiveCell.FormulaR1C1 = ""
Range("C26").Select
ActiveCell.FormulaR1C1 = ""
Range("C27").Select
ActiveCell.FormulaR1C1 = ""
Range("C28").Select
ActiveCell.FormulaR1C1 = ""
Range("C29").Select
ActiveCell.FormulaR1C1 = ""
Range("C30").Select
ActiveCell.FormulaR1C1 = ""
Range("D32").Select
ActiveCell.FormulaR1C1 = ""
Range("D33").Select
ActiveCell.FormulaR1C1 = ""
Range("D34").Select
ActiveCell.FormulaR1C1 = ""
Range("H32").Select
ActiveCell.FormulaR1C1 = ""
Range("I32").Select
ActiveCell.FormulaR1C1 = ""
Range("J32").Select
ActiveCell.FormulaR1C1 = ""
Range("K32").Select
ActiveCell.FormulaR1C1 = ""
Range("L32").Select
ActiveCell.FormulaR1C1 = ""
Range("L33").Select
ActiveCell.FormulaR1C1 = ""
Range("K33").Select
ActiveCell.FormulaR1C1 = ""
Range("J33").Select
ActiveCell.FormulaR1C1 = ""
Range("I33").Select
ActiveCell.FormulaR1C1 = ""
Range("H33").Select
ActiveCell.FormulaR1C1 = ""
Range("H34").Select
ActiveCell.FormulaR1C1 = ""
Range("I34").Select
ActiveCell.FormulaR1C1 = ""
Range("J34").Select
ActiveCell.FormulaR1C1 = ""
Range("K34").Select
ActiveCell.FormulaR1C1 = ""
Range("L34").Select
ActiveCell.FormulaR1C1 = ""
Range("L35").Select
ActiveCell.FormulaR1C1 = ""
Range("K35").Select
ActiveCell.FormulaR1C1 = ""
Range("J35").Select
ActiveCell.FormulaR1C1 = ""
Range("I35").Select
ActiveCell.FormulaR1C1 = ""
Range("H35").Select
ActiveCell.FormulaR1C1 = ""
Range("H38").Select
End Sub
End Sub





Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 788
Default Macro's
yeah... i did.. .sorry.... thank you.. it's working great...

I am going to enroll in a class the teaches excel.. i use it sooo much now
and these dumb books don't seem to teach anything important or at least what
I need to know...

"Norman Jones" wrote:

Hi Chris,

Di you see my comment:

Notr that your code had two headers:

Private Sub CommandButton1_Click()
Sub Clear()

and two closing lines:

End Sub
End Sub

Try:

'=============
Private Sub CommandButton1_Click()
Range("F18:J19, F27:J28, C16:C30, D32:D34, H32:L35"). _
ClearContents
End Sub
'<<=============


---
Regards,
Norman
Microsoft Excel MVP


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

Advanced Search
Display Modes
Linear Mode Linear 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
Macro's 148steve Excel Discussion (Misc queries) 4 November 14th 06 09:39 PM
Hide Macro's in Toolbar / Macro's list sparx Excel Discussion (Misc queries) 2 May 6th 06 08:53 PM
Macro's CraigM Excel Worksheet Functions 2 January 17th 06 01:06 PM
Macro's Ron Excel Discussion (Misc queries) 1 December 27th 05 03:41 PM
macro's ELVIS ARON PRESLEY Excel Worksheet Functions 1 November 10th 04 06:42 PM


All times are GMT +1. The time now is 05:07 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement