Text to rows transformation. Tweets database

[geo_serge]

Hi all! I'm trying to work with tweets database for the floods in Alberta, Ca in 2013. So I need instead of this:
IDtweet Hashtags Date Other columns
123456789 yycflood#abflood#calgary 01/01/2013
this:
IDtweet Hashtags Date Other columns
123456789 yycflood 01/01/2013
123456789 abflood 01/01/2013
123456789 calgary 01/01/2013
The maximum number of hashtags in cell is 13. So, finally all columns except hashtags should stay the same. I tried this code for cells with number of hashtags from 1 to 3 and just for 2 columns:
Option Explicit


Sub Main()


Columns("F:F").NumberFormat = "@"
Dim i As Long, c As Long, r As Range, v As Variant


For i = 1 To Range("F" & Rows.Count).End(xlUp).Row
v = Split(Range("F" & i), "#")
c = c + UBound(v) + 1
Next i


For i = 2 To c
Set r = Range("F" & i)
Dim arr As Variant
arr = Split(r, "#")
Dim j As Long
r = arr(0)
For j = 1 To UBound(arr)
Rows(r.Row + j & ":" & r.Row + j).Insert Shift:=xlDown
r.Offset(j, 0) = arr(j)
r.Offset(j, -1) = r.Offset(0, -1)
r.Offset(j, 1) = r.Offset(0, 1)
Next j
Next i

For i = 3 To c
Set r = Range("F" & i)
Dim arr As Variant
arr = Split(r, "#")
Dim j As Long
r = arr(0)
For j = 1 To UBound(arr)
Rows(r.Row + j & ":" & r.Row + j).Insert Shift:=xlDown
r.Offset(j, 0) = arr(j)
r.Offset(j, -1) = r.Offset(0, -1)
r.Offset(j, 1) = r.Offset(0, 1)
Next j
Next i


End Sub
But it doesn't work. So, I hope on your help

 

[Fluff]

Welcome to the board.
As your sample data shows 3 columns, but your code is working on column F, i'm not quite sure what you need.
This works on 5 columns with column E being split

Sub geo()

    Dim lCnt As Long
    Dim iNum As Integer
    Dim Splt As Variant
    Dim v As Integer
    
    For lCnt = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1
        iNum = Len(Range("E" & lCnt)) - Len(Replace(Range("E" & lCnt), "#", ""))
        If iNum > 0 Then
            Range("E" & lCnt).Offset(1).Resize(iNum).EntireRow.Insert shift:=xlDown
            Range("A" & lCnt).Resize(iNum + 1, 5).FillDown
            Splt = Split(Range("E" & lCnt), ";", -1)
            For v = 0 To iNum
                Range("E" & lCnt + v) = Splt(v)
            Next v
        End If
    Next lCnt

End Sub

HTH

 

[geo_serge]

Thank you for reply. I forgot to say that I have more than 30 columns (the last one is BJ), so I need to just conserve them with the same TweetID. And the highest number of hashtags is 13.

 

[Fluff]

OK, try this

Sub Geo()

    Dim lCnt As Long
    Dim iNum As Integer
    Dim Splt As Variant
    Dim v As Integer
    
    For lCnt = Range("F" & Rows.Count).End(xlUp).Row To 2 Step -1
        iNum = Len(Range("F" & lCnt)) - Len(Replace(Range("F" & lCnt), ";", ""))
        If iNum > 0 Then
            Range("F" & lCnt).Offset(1).Resize(iNum).EntireRow.Insert shift:=xlDown
            Range("A" & lCnt).Resize(iNum + 1, 62).FillDown
            Splt = Split(Range("F" & lCnt), "#", -1)
            For v = 0 To iNum
                Range("F" & lCnt + v) = Splt(v)
            Next v
        End If
    Next lCnt

End Sub