Excel 2013 - everything to the left of a cell

[CRUTHERFORD]

Hi,

I have a spreadsheet that is used for data inputting.

1. How can I get VBA to delete all columns to the left of a cell containing the text "Y/N" until "Y/N" is in cell P25?

2. How can I get VBA to absolute reference cell 'S4' so that if another column is added it will follow the position of what was in cell 'S4' that is now in cell 'T4'?

Thanks,
C

 

[HalfAce]

With question 2. If you give a defined name to cell S4 (and refer to it in the future by it's defined name in code and formulas) it will always be 'that' cell no matter how many columns & rows you insert or delete.

(Select S4, go to the Formulas tab on the ribbon, find Name Manager & Define Name. Click that, give it a name & hit OK.)

For question 1, I'm guessing starting in Row 25, Column O and count right until the first instance of "Y/N" and then delete. (Yes?)
Will there be instances where "Y/N" won't be found in row 25 to the right of column P?

 

[CRUTHERFORD]

Thanks for Question 2

In relation to question 1 - I have the text 'Y/N' in cell P25. I have a command button that inserts columns and when this is clicked the 'Y/N' that was in cell P25 then moves to cell Q25 and so on. I then want another command button that will remove all the additional columns that have been added - My thoughts were to delete every column to the left of the 'Y/N' until 'Y/N' was back in cell P25 which would indicate they have all been deleted?!

Hope that makes sense.

Thanks,
C

 

[HalfAce]

Here's a quick way to start deleting columns from Column P to the right until the value in cell P25 is "N/A".

Sub NA_Demo()
While Range("P25").Value <> "N/A"
    Columns("P:P").Delete
Wend
End Sub

If you were dealing with thousands of columns I might go different way but for a relatively few this should work fine.
Also, if you want to start deleting from somewhere else we can change that easy enough.

Hope it helps.

 

[CRUTHERFORD]

Thanks alot HalfAce!!! Works a charm!

However, i'm struggling to implement the named range for Question 2 (as above)...below is the code i currently have but im not sure how i can make the ranges absolute so if a new row/column is added it will still reference these ce

With ThisWorkbook.Sheets("1.Brief and Matrix")
If (Len(.Range("S4")) = 0) Then
MsgBox "Please enter Job Number. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("G8")) = 0) Then
MsgBox "Please enter Mailing date. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("D29")) = 0) Then
MsgBox "Please ensure volumes are filled in. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("B29")) = 0) Then
MsgBox "Please ensure you have selected Mailing Files. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("B29")) = "Select") Then
MsgBox "Please ensure you have selected Mailing Files. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("E29")) = 0) Then
MsgBox "Please ensure you have specified the Cell Audience. Click ok to fill"
Exit Sub
ElseIf (Len(.Range("N4")) = 0) Then
MsgBox "Please ensure you have specified the Target Volume. Click ok to fill"
Exit Sub
Else

 

[HalfAce]

im not sure how i can make the ranges absolute so if a new row/column is added it will still reference these ce

I'm not sure I understand. I thought you wanted to refer to the same cell(s) no matter where they end up getting moved to.
If you want to use an absolute reference that will always refer to S4 no matter what, you refer to it as $S$4.

Does that help?
If that's not what you meant then I must be missing something...