Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

clear for cell closed file before export data from open file


Hi Guys

I  have   a problem  about clearing   data for  second file  is  closed  when import data from first  file  is  open . the  only  problem   in  the  code is column A for  second  file it  doesn't  clear  the whole cells  before  import  data from first  file  . it  keep  old  numbers in  column A for  second  file . based on  first file  should be 66  elemnts  , but  in  second  file  keep old  numbers after  row 67  should  delete that.

any  idea to  correct  this  problem ,please? 



Selected Answer

Hi Leopard

I think the problem is that your offset is wrong in the code section below:

  Application.ScreenUpdating = False
  ExtBk.Worksheets("REPORT").Cells(1, 1).CurrentRegion.Offset(1, 1).ClearContents
  For i = 2 To lRow
     ExtBk.Worksheets("REPORT").Cells(i, 1).Value = IntBk.Worksheets("TABLE 1").Cells(i, 1).Value


since it clears from B2 (not A2) down and right.  That leaves column A unchanged in ExtBk. The ClearContents line should instead read:

ExtBk.Worksheets("REPORT").Cells(1, 1).CurrentRegion.Offset(1, 0).ClearContents

Hope this fixes things for you.



Hi John ,
always way of count  the  columns in EXCEL  is  different with our realistic life . yes  the  column A should  be  0  not  1 as  I  did it .
  thanks  very  much  for  this spotting
leopard (rep: 82) Mar 7, '23 at 6:56 am
Glad that fixed it. Thanks for selecting my Answer, Leopard.

It's not really counting columns or rows here but the number they are offset (or different) by. Please note that I corrected a typo in my Answer (so it reads cell A2 not B2).
John_Ru (rep: 4937) Mar 7, '23 at 7:02 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login