Link to home
Create AccountLog in
Avatar of upobDaPlaya
upobDaPlaya

asked on

How to remove empty strings

I am currently trying to use paste special to select all null cells and replace the null cells with the first non empty value found in the above cell.  The problems is no blank cells are being selected because the cells have empty strings.

In excel vba is there a way to remove the empty strings and then replace the empty string with the first non empty/blank cell value found from above.
ASKER CERTIFIED SOLUTION
Avatar of Wim_Bl
Wim_Bl
Flag of Belgium image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Ryan
If you want a non-macro method,

B2=if(A2="",B1,A2)

Copy that down column B. Then copy B and paste special(values) into A.
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of upobDaPlaya
upobDaPlaya

ASKER

Hi Wim,

The attached works for blank cells not cell that have empty strings.  Thus if all my cells someone pressed the space bar several times in a cell with no other values then I am defining this as a empty string.  

Thus, how would I loop thru a spreadsheet not just column 3 and test for an empty string or null cell and then if the test is met than fill in with the first cell found above that has an actual value.
Ohh, white space (spaces, enters, tabs) is NOT an empty string.  Careful using words that already have meanings.

Trim() will remove leading and trailing white space.

The macroless version.
B2=if(trim(A2)="",B1,A2)
Hi,

as stated above, a cell with spaces it can be trimmed, works just the same in a macro. Surround Cells(row,col).Value with Trim() first. If you want to loop thru other columns, change the column number in the macro logic. If you want to loop more then one column, add an extra loop, something like:

for row = 1 to 100
      for column = 1 to 100
           If Trim(Cells(row, column)) = "" Then
           ....
           End If
      Next
Next

Of course, change the fixed numbers for the rows and column to the ones you would like to use.


Best regards,

Wim
Thanks...got it..sorry for the incorrect definition.  My only issue now is how do I force the loop to go down in a column direction and not a row direction.  I need to loop column 1, then column 2, etc..my range will typically be from a2 to f15000
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Thanks Bullwinkle that worked, but it only worked if I did cells(row,column).value=cells(row-1,column).  Do you know why I am required to put in the .value (I assume this is a method)...
You are required to do so because Cells(row, column) only returns a reference to the cell. To this reference, you can then assign properties like Value. You don't need to write it to get the value from a cell since it's the default return value, so = Cells(row, column) actually defaults to = Cells(row, column).Value
Excellent explanation and interaction.  Thanks a lot.