TextBox_Change Sub is creating problems in picking up a column - VBA

Question

I am trying to pick up a column from a sheet and delete the Column whenever a new entry is made in the textbox.

Private Sub Ent_TextBox_Change()

Dim Wk As Workbook

Dim Ws As Worksheet


Set Wk = ActiveWorkbook

Set Ws = Wk.Sheets("Main")

Ws.Activate

SearchValue = Ent_TextBox.Value

NumberofCharacters = Len(SearchValue)

lastrow = Sheets("Data").Cells(Rows.Count, 1).End(xlUp).Row

Sheets("Data").Activate

Columns("B:B").Select ' Not picking up this column - Causing error'

Selection.Delete Shift:=xlToLeft

End Sub

Any Suggestions, Why it is not selecting that particular column?

Select method of Range Class failed. – Student of the Digital World May 25 '17 at 15:43 — Student of the Digital World, May 25 '17 at 15:43

score 1 · Accepted Answer · answered May 25 '17 at 15:46

1

Instead of

Sheets("Data").Activate
Columns("B:B").Select ' Not picking up this column - Causing error'
Selection.Delete Shift:=xlToLeft

Try

Sheets("Data").Columns("B:B").Delete Shift:=xlToLeft

answered May 25 '17 at 15:46

RADO

7,733
3
19
33

It Worked. Thanks. What was causing the error actually? Aren't they the same? – Student of the Digital World May 25 '17 at 15:48
1

It's hard to tell without seeing your workbook. As a rule, avoid using 'activate' and 'select' in your code: https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros – RADO May 25 '17 at 16:03
Thanks Rado :) Much appreciate it ! – Student of the Digital World May 25 '17 at 16:10

TextBox_Change Sub is creating problems in picking up a column - VBA

1 Answers1