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 range before copy data from listbox to sheet

0

hi experts

I face the problem of replacing data when copying it from the list box on the userform to the worksheet. What I want it is when copying the data from the list box deleting the old data in the sheet and replacing it with the new data. Rather, replacing the data when copying  from listbox to  sheet . by  the  way   if  you  need knowing  range  in sheet . it  starts from column A : J  and  the  headers from row1 .

I  try  with  this  

  Range("A2").CurrentRegion.ClearContents

this  doesn't work 

this  is  the  whole code 

Private Sub CommandButton1_Click()
Dim r, c As Long
For r = 0 To ListBox1.ListCount - 1
        For c = 0 To ListBox1.ColumnCount - 1
        'Range("A2").CurrentRegion.ClearContents
        Range("A2").Offset(r, c).Value = ListBox1.List(r, c)
        Next
    Next
End Sub

thanks 

Answer
Discuss

Answers

0
Selected Answer

Hi Mussa

Try moving the Clear line outside (and before) the loop and offset it from your title row (changes in bold below)

Private Sub CommandButton1_Click()
Dim r, c As Long
Range("A1").CurrentRegion.Offset(1,0).ClearContents
For r = 0 To ListBox1.ListCount - 1
        For c = 0 To ListBox1.ColumnCount - 1
             Range("A2").Offset(r, c).Value = ListBox1.List(r, c)
        Next c
    Next r
End Sub

(You don't have to add the counter name to the Next statement but I find it helps when debugging).

Can't guarantee this will work since I'm miles from my PC all today. 

Discuss

Discussion

great ! that works.
thanks  John for  your help .
Mussa (rep: 48) May 28, '22 at 6:57 am
Glad that worked for you Mussa
John_Ru (rep: 6142) May 28, '22 at 7:08 am
Add to Discussion


Answer the Question

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