Home > Forum Home > Automating Data Analysis Tasks > Delete cell items in spreadsheet Share

Delete cell items in spreadsheet

Excel Help for Delete Cell Items In Spreadsheet in Automating Data Analysis Tasks


Forum TopicPost Reply Login

Delete Cell Items In Spreadsheet

Rate this:
(3/5 from 1 vote)
ConfusedNeed quick way to delete items in cell but retain items in row that correspond. I have a speadsheet as an example of what I need. I export my sheet to excel, then I have to go row by row cell by cell to fix. I may have 20 to over 1000 rows depending on size of report or how long of time span I am tracking. Any help would be appreciated.
 Posted by on
 
Replies - Displaying 1 to 3 of 3Order Replies By: Most recent | Chronological | Highest Rated
Confused
Rate this:
(4/5 from 2 votes)
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
Next
Application.ScreenUpdating = False
End Sub
 
 Excel Business Forums Administrator
 Posted by on
Confused
Rate this:
(4/5 from 2 votes)
Thank you, it worked perfectly. You don't know how much I appreciate the help!! THANKS!!!!
 Posted by on
Oops
Rate this:
(3/5 from 1 vote)
It is difficult to understand exactly the requirement, but cell content can be removed based on wildcard replace routines that find appropriate cells and replace the content with nothing in order to remove the content.  This way the rows will remain intact.
 Excel Business Forums Administrator
 Posted by on
 Displaying page 1 of 1 

Find relevant Excel templates and add-ins for Delete cell items in spreadsheet in the