Amend VBA Code to Clear Data

Please use code tags when posting. Maybe:

Sub Renleff()

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

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:

Sub Renleffzz()
Dim x As Long
x = ActiveSheet.UsedRange.Columns.count
Columns(x).ClearContents
End Sub

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

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

Sub Renleffyy()
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")

With Sheets("Info")
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

End With

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:

Sub Renleffyy()
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")

With Sheets("Info")
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

End With

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

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?

Sub Renleffyy()
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")

With Sheets("Info")
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
Cells(3, x).Select
Else
Range(Cells(2, x), Cells(y, x)).ClearContents


'Else
'DO SOMETHING ELSE
End If

End With

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

John,

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

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

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.