Overflow error in vba

[imranfarooq]

hi, guys can anyone please help me for this issue? i have 65000+ data i have already vba code with overflow error

i want to merge these type of data

from

A B
1 john mayer
2 calve
3 mitchele j
4 michel

to

A B
1 john mayer calve
2 mitchele j michel

 

[RoryA]

Welcome to the forum.

How would you like us to fix code that we cannot see?

The most usual causes are:
1. You used an Integer variable for a row number. Integers only go up to 32767, so you should use Long instead.
2. You tried to use the Value property for a cell formatted as a date but with a large number in it (it would display in the sheet as ######)

 

[imranfarooq]

Sub DataMerger()
Dim col As Integer
Dim rw As Integer
Dim i As Integer
Dim j As Integer
Dim a As String
Dim b As String
Dim ws As Worksheet
For Each ws In Worksheets
col = ws.UsedRange.Columns.count
rw = ws.UsedRange.Rows.count
Dim col_num As Integer
col_num = 1
For j = 1 To col
For i = 2 To rw
      If ws.Cells(i, col_num) <> "" Then
         b = i
         a = ""
         a = ws.Cells(i, j)
      Else
      If ws.Cells(i, col_num) = "" Then
         a = a & " ^ " & ws.Cells(i, j)
         ws.Cells(i, j).ClearContents
         ws.Cells(b, j) = a
      End If
      End If
Next i
Next j
Next ws
End Sub

this is my code

 

[RoryA]

Then your issue is the first one I mentioned. Change the Integer declarations to Long.

 

[imranfarooq]

i already changed but vba given error again and again

 

[RoryA]

Then why paste the old code? What error and on what line?

 

[imranfarooq]

RoryA thanks
you are right i forget one Integer change to long. its running perfectly

 

[RoryA]

Glad to help.