Delete cell items in spreadsheet

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

Delete Cell Items In Spreadsheet

Rate this: (3/5 from 1 vote)

	Need 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 FODKOD on 11 Apr 2013

Replies - Displaying 1 to 3 of 3

Order Replies By: Most Recent | Chronological | Highest Rated

	Replace content with blank 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 Excel Helper on 11 Apr 2013

	YEAH!!! 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 FODKOD on 12 Apr 2013

	Clarification of the issue and solution 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 Excel Helper on 12 Apr 2013

Displaying page 1 of 1