INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Clear Cell Contents on VBA

Clear Cell Contents on VBA

(OP)
Hi i'm new on vba and want to clear my inputs and autosave on one click.

i have tried this but have an inconsistent results (will clear on exit and sometimes retain its info.)
does anyone who's a vba expert that is willing to help :)

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub Button1_Click()
Range("C18").ClearContents
Range("C21").ClearContents
Range("G6").ClearContents
Range("G7").ClearContents
Range("G8").ClearContents
Range("G22").ClearContents
MsgBox "Thank You"
Application.Quit
Application.DisplayAlerts = False
ActiveWorkbook.Close
If ThisWorkbook = False Then
ThisWorkbook = Save
End If
End Sub

RE: Clear Cell Contents on VBA

It's because of the ActiveWorkbook.Close event is before the ThisWorkbook save event, so it's closing without saving

CODE -->

Private Sub Button1_Click()
Range("C18").ClearContents
Range("C21").ClearContents
Range("G6").ClearContents
Range("G7").ClearContents
Range("G8").ClearContents
Range("G22").ClearContents
MsgBox "Thank You"
ThisWorkbook.Save
Application.Quit
End Sub 

RE: Clear Cell Contents on VBA

(OP)
Thank you kris, i just want to suppress the display alert but make my code out of sequence :)

RE: Clear Cell Contents on VBA

Hiding the alert is something like 'Application.DisplayAlerts = False' (place it just after the MsgBox, I guess).

RE: Clear Cell Contents on VBA

(OP)
hi LRJ, tnx for the reply... kris suggestion is working :)

i have another problem...

how to select cells to print? i am stuck with only 1 cell selected LOL :D

my code below

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Private Sub commandbutton2_click()

Dim filename As String, lineText As String
Dim myrng As Range

filename = ThisWorkbook.Path & "\Results-" & Format(Now, "ddmmyy") & ".txt"

Open filename For Output As #1

Set myrng = Range("A17")

For i = 1 To myrng.Rows.Count
For j = 1 To myrng.Columns.Count
lineText = IIf(j = 1, "", lineText & ",") & myrng.Cells(i, j)
Next j
Print #1, lineText
Next i

Close #1

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

i have tried to

Set myrng = Range(Cells(1, 4), Cells(12, 1))

i don't know why it only reads up to 12th row... type mismatch will occur if i go more than 12 rows.


and if you are too kind to share, is it possible to automatically open the csv/text file after this code? many thanks in advance...

RE: Clear Cell Contents on VBA

Apologies - I misunderstood your post and didn't read the previous answer fully (i.e. the part with the same code as I suggested). D'oh!

Regarding your code above, I wonder if this part is correct:

Range(Cells(1, 4), Cells(12, 1)

This goes through the range D1:A12 - I suspect VBA will struggle with this shape of array.

To open files you use: Workbooks.Open - see here: https://msdn.microsoft.com/en-us/library/office/ff...

RE: Clear Cell Contents on VBA

Excel ranges are defined from the Top Left cell to the Bottom Right cell. In fact, both TopLeft and BottomRight are properties of ranges. So your range is A1:D12.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Clear Cell Contents on VBA

Thanks, SkipVought - I learnt something new!

RE: Clear Cell Contents on VBA

(OP)
thank you LRJ & SkipVought... i think i need to study more about VBA... :)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close