Code deletes old data if I run F8 but not when I press button

RAJESH1960

Banned for repeated rules violations
Joined
Mar 26, 2020
Messages
2,313
Office Version
  1. 2019
Platform
  1. Windows
Hello guys,
Weird it seems but this is happening. When I generate xml the first time, it works perfectly. But the next time when the rows are less than the first time, the code doesn't erase the old data in the Master data sheet. This happens when I run the code with the button.
I ran the code with F8 to check which line needs to be edited. But mysteriously, the code worked perfectly.
First press Generate Master XML and run the code, check the masterdata sheet. Now delete the rows from 11 to end in the paste data sheet and run again. You will see that the master data sheet data is not fully deleted after row 24.
once again using F8 if you run the generate master xml code it will be fully deleted.
I hope I have made it clear what the problem is.
F8 working button not working.xlsm
 
The copy data sheet code also needs to be replaced as it is not clearing the old data. I will try to edit that part and try again.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Rich (BB code):
ClearOldWorkings
    'With Sheets("CopyData")
    '    .Range("A2:AB2", .Range("A2:AB2").End(xlDown)).ClearContents
     '   .Range("AC3:AU3", .Range("AC3:AU3").End(xlDown)).ClearContents
    'End With
I replaced the above with the code below
Rich (BB code):
Sheets("CopyData").Range("A2:AB" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents            ' Erase all but the header row on CopyDatasheet'
    Sheets("CopyData").Range("AC3:AU" & Range("AC" & Rows.Count).End(xlUp).Row).ClearContents            ' Erase all but the header row on CopyDatasheet'
Is that correct.?
 
Upvote 0
Oops... it deleted all the formulas and data from all the working sheets.:p
 
Upvote 0
Sheets("CopyData").Range("A2:AB" & Range("A" & Rows.Count).End(xlUp).Row).ClearContents ' Erase all but the header row on CopyDatasheet' Sheets("CopyData").Range("AC3:AU" & Range("AC" & Rows.Count).End(xlUp).Row).ClearContents
This ain't working. It is deleting all the formulas in AC2 to AU2 even after giving the range as AC3:AU
 
Upvote 0
Time to hit the sack now. Will See you and continue tomorrow JohnnyL. Good Night.
 
Upvote 0
To clear the 'Master Data' sheet data, replace:

VBA Code:
    With Sheets("MasterData")
        .Range("B2:E2", .Range("B2:E2").End(xlDown)).ClearContents
        .Range("F2:I" & Cells(Rows.Count, 6).End(xlUp).Row).ClearContents
    End With

with:

VBA Code:
    Sheets("MasterData").Range("B2:I" & Range("B" & Rows.Count).End(xlUp).Row).ClearContents            ' Erase all but the header row on MasterData sheet

Correction to the previous correction:

To clear the 'Master Data' sheet data, replace:

VBA Code:
    With Sheets("MasterData")
        .Range("B2:E2", .Range("B2:E2").End(xlDown)).ClearContents
        .Range("F2:I" & Cells(Rows.Count, 6).End(xlUp).Row).ClearContents
    End With

with:

VBA Code:
    With Sheets("MasterData")
        .Range("B2:I" & .Range("B" & .Rows.Count).End(xlUp).Row).ClearContents                      ' Erase all but the header row on MasterData sheet
    End With
 
Upvote 0
The copy data sheet code also needs to be replaced as it is not clearing the old data. I will try to edit that part and try again.

Replace:

VBA Code:
' ClearOldWorkings
    With Sheets("CopyData")
        .Range("A2:AB2", .Range("A2:AB2").End(xlDown)).ClearContents
        .Range("AC3:AU3", .Range("AC3:AU3").End(xlDown)).ClearContents
    End With

with:

VBA Code:
' ClearOldWorkings
    With Sheets("CopyData")
        .Range("A2:AB" & .Range("A" & .Rows.Count).End(xlUp).Row).ClearContents                     '   Erase A2:ABx range of data
        .Range("AC3:AU" & .Range("AR" & .Rows.Count).End(xlUp).Row).ClearContents                   '   Erase AC3:AUx range of data
    End With
 
Upvote 0
Solution
Now I remember I had mixed up some codes and created this. Still there is a problem in the ImportMaster sheet, where I have forgotten tp clear the old data. As the code line was already there, I didn't not edit it. But that seems to be the problem.
Rich (BB code):
    Call ClearCommonDataFromSheet(Sheets("ImportMasters"))                                      ' Clear extra data from ImportMasters
 
Upvote 0
If you remember there were 7 buttons to run the code and we brought it down to 3.
 
Upvote 0
Back to the drawing board. Will have to start from the very beginning once again step by step. Hopefully will try and correct it by the time you wake up.😀
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top