VBA Delete .Address and shift up

[tinderbox22]

Hi,

I'm attempting to find multiple instances of a specific text string and then delete and shift cells up. However, if I use the .UsedRange property, there are instances of that same string that I do not want to delete. Is there a way to specify a range rather than using .UsedRange, when finding a cell address? When trying this, I get errors.
My cheat solution is to take my desired range to another blank worksheet, which is not ideal but doable.
Thanks in advance.

 

[Joe4]

In this case, I think it is important for us to have a full grasp of the design/layout of your worksheet, and to under which ranges you want to include and which ranges you do not.
Obviously, that is hard to decipher from written text. It would probably be much better if you could show us an example.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Siddharth Rout]

This can be done without VBA but if you want VBA solution then use Autofilter

Let's say your data is in Col A and you are looking for Apples and delete them.

Here is a screenshot for explaning purpose.

Try this code

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim delRange As Range
    Dim lRow As Long
    Dim SearchText As String
    
    Set wb = ThisWorkbook
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
    
    SearchText = "Apple"
    
    With ws
        '~~> Remove any filters
        .AutoFilterMode = False
        
        '~~> Find last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
       
        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:="=" & SearchText
            Set delRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
        
        '~~> Remove any filters
        .AutoFilterMode = False
    End With
        
    If Not delRange Is Nothing Then delRange.Delete
End Sub

 

[tinderbox22]

In this case, I think it is important for us to have a full grasp of the design/layout of your worksheet, and to under which ranges you want to include and which ranges you do not.
Obviously, that is hard to decipher from written text. It would probably be much better if you could show us an example.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Thank you for the suggestion. Unfortunately, I'm using a work-computer and won't be able to install an add-in.

 

[tinderbox22]

This can be done without VBA but if you want VBA solution then use Autofilter

Let's say your data is in Col A and you are looking for Apples and delete them.

Here is a screenshot for explaning purpose.

View attachment 54673

Try this code

Option Explicit

Sub Sample()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim delRange As Range
    Dim lRow As Long
    Dim SearchText As String
   
    Set wb = ThisWorkbook
    '~~> Change this to the relevant worksheet
    Set ws = Sheet1
   
    SearchText = "Apple"
   
    With ws
        '~~> Remove any filters
        .AutoFilterMode = False
       
        '~~> Find last row
        lRow = .Range("A" & .Rows.Count).End(xlUp).Row
      
        With .Range("A1:A" & lRow)
            .AutoFilter Field:=1, Criteria1:="=" & SearchText
            Set delRange = .Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow
        End With
       
        '~~> Remove any filters
        .AutoFilterMode = False
    End With
       
    If Not delRange Is Nothing Then delRange.Delete
End Sub

Thank you. I will try this and see how it goes. But I'm curious for a non-VBA solution

 

[Siddharth Rout]

Thank you. I will try this and see how it goes. But I'm curious for a non-VBA solution

1. Select the relevant column
2. Go to Data Tab and click on Filter
3. Apply the filter
4. Select first cell and then press SHIFT+ END Key and then press DOWN arrow key
5. Press the DELETE key
6. Remove filter

 

[tinderbox22]

1. Select the relevant column
2. Go to Data Tab and click on Filter
3. Apply the filter
4. Select first cell and then press SHIFT+ END Key and then press DOWN arrow key
5. Press the DELETE key
6. Remove filter

Ah I see. What I failed to mention in my post is that I need this action to occur when another cell reaches a specific number. In other words, I'm counting a range on another worksheet, and once that range count = 8, that range is now considered to be "maxed out." So, I need my cycle function to skip that range. I'm using a Worksheet_Change event to run this macro.

 

[Joe4]

Thank you for the suggestion. Unfortunately, I'm using a work-computer and won't be able to install an add-in.

Though not quite as good (as we cannot copy them), you can also post images.
Then we could at least see what your data structure looks like.

 

[Siddharth Rout]

Ah I see. What I failed to mention in my post is that I need this action to occur when another cell reaches a specific number. In other words, I'm counting a range on another worksheet, and once that range count = 8, that range is now considered to be "maxed out." So, I need my cycle function to skip that range. I'm using a Worksheet_Change event to run this macro.

What you want can be easily achieved with Worksheet_Change event. But I agree with Joe4 that posting an image will help in suggesting an accurate solution

 

[tinderbox22]

What you want can be easily achieved with Worksheet_Change event. But I agree with Joe4 that posting an image will help in suggesting an accurate solution

Fair point. I will post my examples today at some point. I'm not as familiar with worksheet_change events, so I'm still having a bit of trouble. Although the Autofilter macro that you suggested did work as intended. I tweaked it to delete and shift up rather than deleting an entire row, as there is other data in those rows that are still needed.