Calculate total space usage from spreadsheet

Good Morning All,

I wrote the below code to try calculate the total amount of space used on an FTP site.
I have a list of all the different folders and the space they take up is in column 'B'. However the amount of space is followed by a 'KB' or 'MB' or 'GB'. So What I'd like to do is calculate the total amount of space in Gigabytes. I'm not sure what data types to set everything as for this in order to get this to work.
The Macro runs without any errors, but only returns a 0.

Any help would be greatly appreciated.

Cheers

R

Sub StorageTotal()
Dim FTP As Worksheet
Dim RowNo As Long, LastRow As Long
Dim Total As Integer
Dim GB As String, MB As String, KB As String
Dim GBRT As Double, MBRT As Double, KBRT As Double
Dim TotalCalc As Double

Set FTP = ThisWorkbook.Worksheets("FTP Register")

    With FTP
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
            For RowNo = 2 To LastRow
            
               If InStr(1, FTP.Cells(RowNo, "B"), "GB", vbTextCompare) = "1" Then
                GB = FTP.Cells(RowNo, "B")
                GB = Right(GB, Len(GB) - 2)
                GBRT = GBRT + GB
                
               ElseIf InStr(1, FTP.Cells(RowNo, "B"), "MB", vbTextCompare) = "1" Then
                MB = FTP.Cells(RowNo, "B")
                MB = Right(MB, Len(MB) - 2)
                MBRT = MBRT + MB
                
               ElseIf InStr(1, FTP.Cells(RowNo, "B"), "KB", vbTextCompare) = "1" Then
                KB = FTP.Cells(RowNo, "B")
                KB = Right(KB, Len(KB) - 2)
                KBRT = KBRT + KB
               
               ElseIf InStr(1, FTP.Cells(RowNo, "B"), "Empty", vbTextCompare) = "1" Then
               End If
            Next
         
         TotalCalc = GBRT + (MBRT / 1024) + ((KBRT / 1024) / 1024)
         
         FTP.Cells(17, "L") = TotalCalc
    End With
        
End Sub

Updated but still not working

Sub StorageTotal()
Dim FTP As Worksheet
Dim RowNo As Long, LastRow As Long
Dim Total As Integer
Dim GB As String, MB As String, KB As String
Dim GBCon As Double, MBCon As Double, KBCon As Double
Dim GBRT As Double, MBRT As Double, KBRT As Double
Dim TotalCalc As Double

Set FTP = ThisWorkbook.Worksheets("FTP Register")

    With FTP
        LastRow = FTP.Cells(.Rows.Count, "A").End(xlUp).Row
            For RowNo = 2 To LastRow
            
               If InStr(1, FTP.Cells(RowNo, "B"), "GB", vbTextCompare) = "1" Then
                GB = FTP.Cells(RowNo, "B")
                GB = Right(GB, Len(GB) - 2)
                GBCon = Val(GB)
                GBRT = GBRT + GBCon
                
               ElseIf InStr(1, FTP.Cells(RowNo, "B"), "MB", vbTextCompare) = "1" Then
                MB = FTP.Cells(RowNo, "B")
                MB = Right(MB, Len(MB) - 2)
                MBCon = Val(MB)
                MBRT = MBRT + MBCon
                
               ElseIf InStr(1, FTP.Cells(RowNo, "B"), "KB", vbTextCompare) = "1" Then
                KB = FTP.Cells(RowNo, "B")
                KB = Right(KB, Len(KB) - 2)
                KBCon = Val(KB)
                KBRT = KBRT + KBCon
               
               ElseIf InStr(1, FTP.Cells(RowNo, "B"), "Empty", vbTextCompare) = "1" Then
               End If
            Next
         
         TotalCalc = GBRT + (MBRT / 1024) + ((KBRT / 1024) / 1024)
         
         FTP.Cells(17, "L") = TotalCalc
    End With
End Sub

Can you attach a sample file for us to 'play' with..

I am thinking you could incorporate autofilter then change each value to a common denominator (GB).. you could change the actual value or use a helper column..

Here ya go

Cheers Dude!

Is there anyway I can shorten the total to .00 decimal places, Then stick a Gb on the end?

It's cool, I got it. Cheers for all your help!