Katakana to hiragana VBA

[llorcs]

Hi all!

I have an excel file where I have to change Katakana characters to Hiragana. I've been using StrConv + vbHiragana for some time, however, it is really slow since I am using it in a range for a 1000 rows (two times). After searching around, I've found the following code, which seems like a different approach and looks really fast. My only problem is, that it only works if I select the specified row and column (e.g. from A1:A10) and then if I run it, it does the job perfectly. I need to tweak this so it automatically does it in a range and displays the results on a different sheet.　(From a1:A1000 + D1:D1000)
Like this:

Sheet1:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]山田[/TD]
[TD]ヤマダ[/TD]
[TD]太郎[/TD]
[TD]タロウ[/TD]
[/TR]
</tbody>[/TABLE]

Sheet2:
[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]山田[/TD]
[TD]やまだ[/TD]
[TD]太郎[/TD]
[TD]たろう[/TD]
[/TR]
</tbody>[/TABLE]



Here is the code I've found:

Sub Comm()
Dim i, gyos, retus, rwsu As Integer
Dim KATA, HIRA As String


gyos = ActiveCell.Row
retus = ActiveCell.Column
rwsu = Selection.Rows.Count - 1


For n = gyos To gyos + rwsu
   KATA = Cells(n, retus)
   HIRA = StrConv(KATA, 32)
   Cells(n, retus) = HIRA
Next n


End Sub

Thank you for the help!

 

[RickXL]

Hi,

I am not sure how I create cells with Katakana in them so I can't test this:

Sub Convert()
    Dim arr  As Variant
    Dim i    As Long
    Dim j    As Long
    
    arr = Worksheets("Sheet1").Range("A1:D1000")
    For i = 1 To UBound(arr, 1)
        For j = 1 To UBound(arr, 2)
            arr(i, j) = StrConv(arr(i, j), 32)
        Next
    Next
    Worksheets("Sheet2").Range("A1:D1000") = arr
End Sub

It reads a fixed range from Sheet1, performs the conversion then writes it back to Sheet2.

You can change the worksheet names by overtyping.

Regards,