Delete Cells, Shift Left Based on Content

[justDave]

Hi all,

Could you please help me draw up what, I hope, is a simple macro.

I have a header row in A and what I want to achieve is for certain cells to be deleted if they contain certain words, and then everything remaining in A be shifted Left.

The criteria text should be:

STDDEV
EXPVALUE

Is this possible?

Thanks in advance for any help you can provide.

Regards,

 

[Joe4]

Do you want just this to just apply to your "header" row, or you entire data?

If the header row, is that just in row 1?
If the entire data set, can you desribe the size (dimensions) of your data set (will it have a defined set of rows or columns)?

 

[justDave]

Hi Joe,

Yes, it is only the header row (in row 1) which I need to apply it to.

I don't know if this will affect anything but the number of columns in that row will change each time - the current one I'm looking at goes up to BB1, but I've seen others go up to HC1.

Kind regards,

Dave

 

[Joe4]

This should handle that:

Sub MyDeleteColumns()
 
    Dim lastcol As Integer
    Dim i As Integer
    
    Application.ScreenUpdating = False
    
'   Find last used column in row 1
    lastcol = Cells(1, Columns.Count).End(xlToLeft).Column
    
'   Loop through all columns, from last to first
    For i = lastcol To 1 Step -1
        If InStr(Cells(1, i), "STDDEV") > 0 Or InStr(Cells(1, i), "EXPVALUE") > 0 Then
            Cells(1, i).Delete Shift:=xlToLeft
        End If
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

 

[justDave]

Absolutely perfect, thanks Joe!

 

[zaHawk]

Is there a way the resfresh the worksheet.. so it is a new quote sheet ?
I use a page to temporary put in stock Items with prices.. but am looking for a way to click a button.. and all fields (that are typed in) can instantly be cleared.

There is a long way.. to punch each cell into the button ... (done ages ago).. but is there a way to say clear all unprotected cells?
This way it will be much easier to format.. can it be done ?

Thanks

 

[Joe4]

Is there a way the resfresh the worksheet.. so it is a new quote sheet ?
I use a page to temporary put in stock Items with prices.. but am looking for a way to click a button.. and all fields (that are typed in) can instantly be cleared.

There is a long way.. to punch each cell into the button ... (done ages ago).. but is there a way to say clear all unprotected cells?
This way it will be much easier to format.. can it be done ?

Thanks

What does this have to do with the original question that was posted (and asked by someone else)?

If you have a question of your own, you should post it to a new thread instead of on the end of someone else's (unless it is very similar to the original question asked).

 

[zaHawk]

My bad... I thought it had to do with deleting certain cells.. but guess not
My object was also not to start a similar posts.. when one exists.. guess more is better
I will start a new post then.. Thanks