Hi.
I have a macro that deletes cells from A1 till A10.
What I'd like the macro to do for me is if the macro finds a specific cell
name (string), then it doesn't delete the cells having that string. If it
doesn't, then delete every cell not cointaining that scheme.

Example: If I have the letter "C" in cell A2 and A7, I'd like the macro to
delete every other cell, ranging from A1 till A10 (excluding A2 and A7 that
has the "C" string).

Best regards

Hi there.
I have been having issues with my desktop, so I'm posting this from the
laptop.
The macro is doing what I need but I do not want the cells containing the
specific string to move up.
Example:
Range=A1:A10
"C" is in A6
"7" in A3 and A8.
I'd like the macro to delete the A3 and A8 cells but leave "C" in the same
cell (A6).
As the macro is, both A3 and A8 are deleted but "C" moves to A1 (I want it
to stay on the same cell (A6).
Thank you.

"sebastienm" wrote:

Hi,
Try the following:

Sub test()

Dim rgToSearch As Range, cell As Range, rgResult As Range
Dim strToSearchFor As String

'---------- CHANGE HERE ------------------------------------------------
Set rgToSearch = ActiveSheet.Range("A1:A10") 'Range to search
strToSearchFor = "C" 'String to search for

'----------------------------------------------------------------------------

'Find non-matching cells
For Each cell In rgToSearch.Cells
If cell.Text < strToSearchFor Then 'If no match
If rgResult Is Nothing Then 'Add the cell to the result range
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
End If
Next cell

'Process the result range
If Not rgResult Is Nothing Then
'rgResult.ClearContents 'clear the contents of these cells
rgResult.Delete xlShiftUp 'delete
End If
End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"maia" wrote:

Hi.
I have a macro that deletes cells from A1 till A10.
What I'd like the macro to do for me is if the macro finds a specific cell
name (string), then it doesn't delete the cells having that string. If it
doesn't, then delete every cell not cointaining that scheme.

Example: If I have the letter "C" in cell A2 and A7, I'd like the macro to
delete every other cell, ranging from A1 till A10 (excluding A2 and A7 that
has the "C" string).

Best regards

Hi,
Seems like you just want to clear (and not delete) non-matching cells, am i
right?
I implemented both (clear & delete) in the code i posted. Just uncomment the
line in the 'Process the result range' section:
rgResult.ClearContents 'clear ...
and comment out the line
'rgResult.Delete xlShiftUp 'delete

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"maia" wrote:

Hi there.
I have been having issues with my desktop, so I'm posting this from the
laptop.
The macro is doing what I need but I do not want the cells containing the
specific string to move up.
Example:
Range=A1:A10
"C" is in A6
"7" in A3 and A8.
I'd like the macro to delete the A3 and A8 cells but leave "C" in the same
cell (A6).
As the macro is, both A3 and A8 are deleted but "C" moves to A1 (I want it
to stay on the same cell (A6).
Thank you.

"sebastienm" wrote:

Hi,
Try the following:

Sub test()

Dim rgToSearch As Range, cell As Range, rgResult As Range
Dim strToSearchFor As String

'---------- CHANGE HERE ------------------------------------------------
Set rgToSearch = ActiveSheet.Range("A1:A10") 'Range to search
strToSearchFor = "C" 'String to search for

'----------------------------------------------------------------------------

'Find non-matching cells
For Each cell In rgToSearch.Cells
If cell.Text < strToSearchFor Then 'If no match
If rgResult Is Nothing Then 'Add the cell to the result range
Set rgResult = cell
Else
Set rgResult = Application.Union(rgResult, cell)
End If
End If
Next cell

'Process the result range
If Not rgResult Is Nothing Then
'rgResult.ClearContents 'clear the contents of these cells
rgResult.Delete xlShiftUp 'delete
End If
End Sub
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"maia" wrote:

Hi.
I have a macro that deletes cells from A1 till A10.
What I'd like the macro to do for me is if the macro finds a specific cell
name (string), then it doesn't delete the cells having that string. If it
doesn't, then delete every cell not cointaining that scheme.

Example: If I have the letter "C" in cell A2 and A7, I'd like the macro to
delete every other cell, ranging from A1 till A10 (excluding A2 and A7 that
has the "C" string).

Best regards