Clarification Of The Issue And Solution: Delete cell items in spreadsheet Excel help response (12 Apr 2013)

No problem. We should probably clarify the issue and solution here so that anyone can also benefit if they have the same issue.

The issue was related to cells having content separated by line breaks. Cells in adjacent columns for the same row also had content matching the line numbers of the other cells in that row. We needed to remove lines based on a criteria and also ensure that those lines would be removed in the other columns for that row.

To solve this, we created a macro which put the cell content into an array, removed items on the criteria and used the placeholder (line numbers) where data was removed to remove the same line numbers in the other cells.

VBA Code:

'Code commented to explain

Sub Remove_Non_C()

Application.ScreenUpdating = False

Dim lrow, j, k, l As Long

Dim strType, strTmp, newType, newTmp As String

Dim arrType, arrTmp As Variant

Dim iType As Variant

'get last row

lrow = Range("F50000").End(xlUp).Row

'loop Types

For Each iType In Range("F2:F" & lrow)

'build array

strType = iType.Text

arrType = Split(strType, Chr(10))

'Loop array

newType = ""

For j = 0 To UBound(arrType)

'check for non C

If Trim(arrType(j)) <> "C" Then

'remove

arrType(j) = ""

Else

'build new string

newType = newType & arrType(j) & Chr(10)

End If

Next j

'Loop Adjacent cells

For k = -2 To 6

'not itself

If k <> 0 Then

'build array

strTmp = iType.Offset(0, k).Text

arrTmp = Split(strTmp, Chr(10))

'loop array

newTmp = ""

For l = 0 To UBound(arrTmp)

'check for non C place in Type

If l <= UBound(arrType) And arrType(l) = "" Then

'remove

arrTmp(l) = ""

Else

'build new string

newTmp = newTmp & arrTmp(l) & Chr(10)

End If

Next l

'replace contents of cell with new string

iType.Offset(0, k).Formula = newTmp

End If

Next k

'replace Type with non C version

iType.Formula = newType

Application.ScreenUpdating = False

End Sub

Clarification of the issue and solution

Excel Help for Clarification Of The Issue And Solution in Automating Data Analysis Tasks

Clarification Of The Issue And Solution