Clear Cell Contents on VBA

[mitzen]

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

 

[kris44]

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

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

 

[mitzen]

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

 

[LRJ]

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

 

[mitzen]

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

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...

 

[LRJ]

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/ff194819.aspx

 

[SkipVought]

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 A112.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[LRJ]

Thanks, SkipVought - I learnt something new!

 

[mitzen]

thank you LRJ & SkipVought... i think i need to study more about VBA...