Deleting columns other than those that a specific word in row A
 

I have a requirement to delete columns in my spreadsheet based upon the value
that appears in column A. I recentlt posted a similar request on here and
got the following suggestion:

Sub Valuation()

' Dim rng as Range, sStr as String, i as Long
Set Rng = Cells(1, "IV").End(xlToLeft)
For i = Rng.Column To 1 Step -1
sStr = LCase(Cells(1, i).Value)
If sStr < "fondsname" And _
sStr < "wertpapierkurzbez" And _
sStr < "gw wpi isin" And _
sStr < "stücke/nominale" And _
sStr < "effektenkurs" And _
sStr < "kurswert in bw" And _
sStr < "offene forderungen" Then
Cells(1, i).EntireColumn.Delete
End If
Next

End Sub

I used a variation of this on other spreadsheets and it worked fine,
however, wehen I used the strings that I have used above i run into problems
and I don't get the result i am hoping for. I believe that there may be
hidden spaces within the cell or something. Does anyone know how to get
around the problem ?

Thanks

Deleting columns other than those that a specific word in row A
 

I think you're going to have to share some more information.

What bad things happen/don't happen when you run your code?

If a column is being deleted that should be kept, what was in row 1 of that
column?

If a column is being kept that should be deleted, what was in row 1 of that
column?

If there are extra spaces, maybe you could trim() the values.

sstr = trim(lcase(cells(1,i).value))



Sean wrote:

I have a requirement to delete columns in my spreadsheet based upon the value
that appears in column A. I recentlt posted a similar request on here and
got the following suggestion:

Sub Valuation()

' Dim rng as Range, sStr as String, i as Long
Set Rng = Cells(1, "IV").End(xlToLeft)
For i = Rng.Column To 1 Step -1
sStr = LCase(Cells(1, i).Value)
If sStr < "fondsname" And _
sStr < "wertpapierkurzbez" And _
sStr < "gw wpi isin" And _
sStr < "stücke/nominale" And _
sStr < "effektenkurs" And _
sStr < "kurswert in bw" And _
sStr < "offene forderungen" Then
Cells(1, i).EntireColumn.Delete
End If
Next

End Sub

I used a variation of this on other spreadsheets and it worked fine,
however, wehen I used the strings that I have used above i run into problems
and I don't get the result i am hoping for. I believe that there may be
hidden spaces within the cell or something. Does anyone know how to get
around the problem ?

Thanks

--

Dave Peterson