• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Count number of lines & comma seperated values in a VBA String variable

inddon

Member
Hello There,

I have a certain chunk of comma seperated values stored in a VBA string variable. In the active worksheet, the user chooses a cell, where it should be pasted.
Before it could pasted, I want to make sure there is enough room (rows/columns) for this string value and it does not overwrite any existing information.
I would like to have a function which would do the following:

StringValue: (This is a VBA variable data type String with multiple lines (not stored in any cell value)
"Label1,Label2,Label3,Label4,Label5
One,Two,Three,Four,Five
Six,Seven,Eight,Nine,Ten
Eleven,Twelve,Thirteen,Fourteen
Fifteen,Sixteen,Seventeen,Eighten
Nineteen,Twenty,Twenty One, Twenty Two, Twenty Three"

In the above there are 6 Rows & 5 columns

In the Variable String Value:
StringRows = Find the total number of lines (rows).
StringColumns = From the first line, find the number of columns (identification character ','). How many ',' are there + 1

In the Worksheet, from the cursor position:
WorksheetRows = Find the number of empty rows to the down between cursor position and the first non empty cell.
WorksheetColumns = Find the number of empty columns to the right between cursor position and the first non empty cell.

Matching:
If WorksheetRows > StringRows And
WorksheetColumns > StringColumns Then
Return True
Else
Return False
End if


I was able to get the rows of the string value and the worksheet working as below. Wonder if there would be a way to combine the above functionality in one function:

I have attached a sample workbook for your reference.

Code:
Function CheckRows(pRange As String, pOutput) As Boolean
  Dim lTotalRowsOutput

  lTotalRowsOutput = Split(pOutput, Chr(10))
  lTotalRowsOutput = UBound(lTotalRowsOutput)

  Dim lCurrentRowNumber As Long, lFirstNonBlankCell As Long
  Range(pRange).Select
  lCurrentRowNumber = ActiveCell.Row
  Selection.End(xlDown).Select
  lFirstNonBlankCell = ActiveCell.Row

  If (lFirstNonBlankCell - lCurrentRowNumber) < lTotalRowsOutput Then
     CheckRows= False
  Else
    CheckRows= True
  End If
  Range(pRange).Select
End Function

Thank you. Look forward to hearing from you.

Regards,
Don
 

Attachments

Last edited:

Marc L

Excel Ninja
Hi, try this function as a beginner starter :​
Code:
Function IsEnoughRoom(A$, ByVal V) As Boolean
    Dim C%, R&
        V = Split(V, vbLf)
    With Range(A)
        If IsEmpty(.Cells(1, 2)) Then C = .End(xlToRight).Column - .Column - 1
        If IsEmpty(.Cells(2)) Then R = .End(xlDown).Row - .Row - 1
    End With
         IsEnoughRoom = UBound(Split(V(0), ",")) < C And UBound(V) < R
End Function
Do you like it ? So thanks to click on bottom right Like !
 
Top