Amend VBA Code to Clear Data

Please use code tags when posting. Maybe:

Please Login or Register  to view this content.

John,

The above code works, now...

...if answer is vbNo, what is the code to go to the last column populated, erase it then continue with the remainder of the code? (Clear contents then copy download to the next open column (one just cleared)).

Thanks.

Renleff

Try:

Please Login or Register  to view this content.

Test it first.

John,

The code did not erase the intended column. What if I use the following:

If rng2 = "" Then This would identify the next open column
XXXXX

John,

The code did not erase the intended column. What if I use the following:

If rng2 = "" Then This would identify the next open column
XXXXX Code here would take me one column to the left to erase
Range(Selection, Selection.End(xlDown)).Select These next two lines would clear contents
Selection.ClearContents

Whatever the proper code above is, it would be invoked when the answer to the message box is "vbNO". vbYES would bypass this part o fhte code and fill in the next available column.

Thanks.

Tom

Can you provide a sample? Which Column if any got erased?

John,

Here is a sample file. The button to the right of the data invokes the code. It is macro Column_Fill. When I built in your code, it seemed to erase column O on the Info tab.

To give you a bit of context, each month-end, my controllers will download their respective trial balances in to this program to create a suite of reports and management metrics. No problem on the first time they download information; the code works as intended. If they must download the same month's trial balance again, with updated information, the code will place the updated information in the next month, not "update" the current month. This is why I need a trigger, the Message Box to erase the current month's data if it needs to be replaced.

Thanks.

Renleff

P.S. - a file should be attached

Download Report.xlsm

For your sample this should identify and clear the last used column. I didn't know you had a header row.

Please Login or Register  to view this content.

John,

The routine from your latest reply I added into my VBA code. Pressing the No from the Message Box actually erased the data in column D in the Download sheet. In the Info sheet, the empty fields next to the latest data (which should have been erased) were highlighted.

I have attached the code below because the file attachment would not add my new file.

Thanks.

Renleff




Sub Column_Fill() ' Used for first time copying of download to info tab

Dim rng As Range ' Range of argument answers
Dim rng2 As Range ' Each instance of an argument answer
Dim rng3 As Range ' Source information
Dim i As Integer
Dim x As Long

Set rng3 = Worksheets("Download").Range("d2:d1100")
Set rng = Worksheets("Info").Range("e3:y1100")

If MsgBox("Is this your first download of this month's info?", vbYesNo) = vbNo Then
'DO SOMETHING

x = Cells(3, Columns.Count).End(xlToLeft).Column
y = Range("B" & Rows.Count).End(3).Row
Range(Cells(2, x), Cells(y, x)).ClearContents


Else
'DO SOMETHING ELSE
End If



For Each rng2 In rng
If rng2 = "" Then
rng3.Copy ' range (source) to copy
rng2.PasteSpecial (xlPasteValues) ' where to copy as a value rather than formula
Application.CutCopyMode = False
Exit Sub
Else
'Do Nothing
End If
Next rng2

End Sub

Maybe:

Please Login or Register  to view this content.

Maybe:

Please Login or Register  to view this content.

Please use code tags when posting See Forum Rule No. 3.

http://www.excelforum.com/forum-rule...rum-rules.html

John,

The code you provided will only populate the download info in column E on the info tab. When column E has data, and vbYes is invoked on the Message box, data is overwritten in column E rather than go to the next open column, column F. This is actually how vbNO should work.

When the answer to the Message box is vbNO, which is replacing current month's data and how vbYES is working, the download information starting in column C is erased. If I invoke the code again the information in column is erased.

In summary, replacing current month's info with an update works through vbYES; need code for vbYES to work as vbNO and vbYES find next open column.

Thanks.

Renleff

Try?

Please Login or Register  to view this content.

John,

The macro would not work and stooped at the code below.

Please Login or Register  to view this content.

Range(Cells(2, x), Cells(y, x)).ClearContents

Please Login or Register  to view this content.

Thanks.

Renleff

Appears to work for me on your sample?

Please refer to Forum Rule No. 3 when posting.

http://www.excelforum.com/forum-rule...rum-rules.html

I opened the file above and ran the macro answering, "Yes", and "No". "Yes" placed the data in the correct column - next open one (November); "No" also placed the data in the next open column (December). It should have replaced the "Yes" data in November's column..

Here is a test for you to run and check your code:

Place some new figures in download then invoke the macro and answer "Yes". The new data should be in the November column on the Info sheet. Now change the data again on the download sheet, invoke the macro and answer "No". On the Info tab, the data in the October column should have changed, not gone into November's column.