Increment Column Selection with InputBox

[vijyat]

Hi,
I hope I am in the right place. This is my first post since I joined the forum 2 days ago. I have been reviewing posts and modifying my script however, I need some help please. I would first like to thank Zack, Smozgur, Anne, Ken, Johnski and several others for their expertise as I browsed through the forum for answers.

Currently using Excel 2013
Issue : Need help with Incrementing Columns through InputBox and using selection in Select Case rather than individual entries. This script references/ calls another sub at the end which does its own thing. Hence the selection is important.

Code:


Option Compare Text 'selecting column based on user input
Sub selColumn()
Dim col As Range
Dim CaseMode As String * 1
On Error Resume Next
Err.Clear
With Application
.ScreenUpdating = False
.EnableEvents = False
CaseMode = InputBox("Enter A for column A..")
For Each col In ActiveSheet.UsedRange
If Err.Number = 0 Then
Select Case CaseMode
Case Is = "A"
.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Select
Case Is = "B"
.Range("B1", .Cells(.Rows.Count, "B").End(xlUp)).Select
' and so on ....for all Case is "A to Z".
' I would like to loop/increment the above Cases rather than typing it individually (please help)
End
End Select
End If
Next col
End With
End Sub

I apologize in advance if I am not following the forum rules or if I need to add anything else as this is my first post. I would appreciate any help on this. Thanks a lot

[patel]

I can not understand the goal of your code, but you can use column number instead of letter and eliminate select case

Sub selColumn()
Dim col As Range
Dim CaseMode As String * 1
With Application
.ScreenUpdating = False
.EnableEvents = False
CaseMode = InputBox("Enter column Number")
ncol = val(CaseMode)
For Each col In ActiveSheet.UsedRange
.Range(ncol, .Cells(.Rows.Count, "A").End(xlUp)).Select
Next col
End With
End Sub

[HaHoBe]

Hi, patel,

I wonder how

ncol = val(CaseMode)
woirks when being applied in

.Range(ncol, .Cells(.Rows.Count, "A").End(xlUp)).Select
since this line should refer to Application which would need Workbook and Worksheet first and AFAIR doesn´t excpect only a numeric value for a range description. My Excel2013 throws an error 1004 on that line (as I expected it to do).

Please elaboreate your code.

@vijyat:
If you turn off events in a macro you should turn these on again before leaving the macro or make sure that any error would recover teh originla setting.

Holger

[vijyat]

Thank you for the quick response. Patel I tried your code and it gives me an error 400 in the Msgbox. I apologize if I wasn't clear on my goal but I am attaching the sample data file, that will give you more idea. The first part of the code "Sub selColumn" makes the selection in the used range and that calls the subroutine "Sub replaceChars" to remove characters. Ultimate goal is data sanitizing.

Holger, I am not sure what AFAIR means as I am new to programming. I usually follow a logical approach so you'll probably have to babysit me.:help

[HaHoBe]

Hi, vijyat,

AFAIR is an acronym which stands for As Far As I Remember.

The asterik you want to search for is a wildcard and would remove all characters in the given Column, you must use "~*" instead of just "*". Maybe try it like this where the range is not selected but transferred as parameter (you could consider passing the characters to remove as a ParamArray):

Option Explicit
Option Compare Text ' makes user input case-insensitive, so it doesn't matter if user enters "P" or "p"


'selecting column based on user input

Sub selColumn()
Dim CaseMode As String * 1

On Error Resume Next
Err.Clear

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

CaseMode = UCase(InputBox("Enter A for column A.."))

If (Len(CaseMode) > 1) Or (Asc(CaseMode) < 65 Or Asc(CaseMode) > 90) Then
MsgBox "No access"
GoTo Exit_Here
End If
With ActiveSheet
Call replaceChars(.Range(.Cells(1, CaseMode), .Cells(Rows.Count, CaseMode).End(xlUp)))
End With

Exit_Here:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub


'replace special characters
Sub replaceChars(rng As Range)

Dim toRemove As Variant
Dim lngCounter As Long

toRemove = Array("(", "-", "~*", "_") 'enter characters here within quotes as many as you want seperated by ,

For lngCounter = LBound(toRemove) To UBound(toRemove)
rng.Replace What:=toRemove(lngCounter), Replacement:="", MatchCase:=False 'Replace False with True if case is important
Next lngCounter

End Sub

Ciao,
Holger

[SamT]

Sub selColumn()
Dim Col As Long
Dim ColName As String
Dim SeletedRange As Range
On Error Resume Next
Err.Clear

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ColName = InputBox("Enter A for column A..")
Col = Columns(ColName).Column
'Cells(Rows.Count, Col).End(xlUp).Select
Set SelectedRange = Cells(Rows.Count, Col).End(xlUp)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

[vijyat]

Holger: You Sir are a genius.:bow: I completely forgot about the wildcard. I tried the script it works great. I was hoping if you can kindly explain me what this line does, I like to learn as I go. : "If (Len(CaseMode) > 1) Or (Asc(CaseMode) < 65 Or Asc(CaseMode) > 90) Then" Also the code works great for columns A-Z however, it does not do anything for the next set of columns from AA to AZ .
Thank you

Hi Sam,
Thank you for looking into it aswell. I tried your code but it doesn't select the range.

Sub selColumn()
Dim Col As Long
Dim ColName As String
Dim SeletedRange As Range ' I fixed this to SelectedRange
On Error Resume Next
Err.Clear

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ColName = InputBox("Enter A for column A..")
Col = Columns(ColName).Column
'Cells(Rows.Count, Col).End(xlUp).Select ' Was this suppose to be commented ? it has a ' prefix .
Set SelectedRange = Cells(Rows.Count, Col).End(xlUp)

With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

[HaHoBe]

Hi, vijyat,

Dim CaseMode As String * 1
makes the return of the InputBox 1 character wide so that only means the first 26 columns by character.

I wonder why you think you would need to step through all the cells and columns one by one - you may refer to the entire used range on the sheet at once with the commands.


Len(CaseMode) > 1
will check for the length of the code from the inputbox and not allow more than just one character.


Asc(CaseMode) < 65 Or Asc(CaseMode) > 90
will check for the ASCII value of the character and only allow A to Z.

No need to select or set a range, just pass the range on as a parameter to the second procedure.

Ciao,
Holger

[vijyat]

Hi Holger,

This is just a subset of a bigger project I am working on. The above code removes characters in the column that I need. For Ex: if I have a header " Phone Number" and the entries are 123-234-3434 or (123)234-3434, and any other formats. I am trying to form a baseline for all the phone numbers within the column, but if I use the ActiveSheet.UsedRange that would mean that under my email address or fname, lname column if I have those characters, it would delete them aswell and change the meaning of the data completely. Also I can use the above code to call another function to change case i.e UPPERCASE or LOWERCASE based on the range selection .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)).Select. hence this code will work for multiple functions depends on which subroutine I am calling. Hope you get the idea.

Thanks again for the explanation. So I modified the "Len(CaseMode) >1 to Len(CaseMode)>2 and that works for additional columns as well. But now my other code for the change case does not work. I tried to comment this part and call my other subroutine, however it does not work Call replaceChars(.Range(.Cells(1, CaseMode), .Cells(Rows.Count, CaseMode).End(xlUp)))

[SamT]

This will select the bottom used cell in the chosen column

Cells(Rows.Count, Col).End(xlUp).Select

This will set the variable SelectedRange to that same cell

Set SelectedRange = Cells(Rows.Count, Col).End(xlUp)

My bad, I thought you only wanted to select the last cell in a column.

This selects the entire used part of the column.
Range(Cells(1, Col), Cells((Rows.Count, Col).End(xlUp)).Select

[vijyat]

Thank you for your efforts Sam and Holger. It worked. :)
Here is the final code that I used. I just had a question, I was able to understand majority of the code. Can someone please explain me why we use the "Sub xyz(rng as range)" what is the purpose of putting rng as range or something other within the subroutine ? I shall try to play around more with the code and mark it solved. Many thanks


VB:
Option Explicit
Option Compare Text ' makes user input case-insensitive, so it doesn't matter if user enters "P" or "p"

Sub selColumn()
Dim Col As Long
Dim ColName As String
Dim SelectedRange As Range
On Error Resume Next
Err.Clear

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

ColName = InputBox("Enter A for column A..")
Col = Columns(ColName).Column
Set SelectedRange = Range(Cells(1, Col), Cells((Rows.Count), Col).End(xlUp)).Select

'Call replaceChars : reference to another subroutine
'Call changeCase : reference to another subroutine
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

[SamT]

Inside Sub xyz, something is going to happen with rng.

sub xyz doesn't care which range it works on, so we have to give it a range


Sub testxyz()
Dim MyRange As Range
Set MyRange = Range ("A1")
xyz MyRange

Set MyRange = Range("C3")
xyz MyRange

End sub



Sub xyz(rng as range)
rng.Interior.ColorIndex = 3
End Sub

Sub Testxyz calls sub xyz two times giving it a different range.

Sub Testxyz only uses two ranges, sub xyz works on every range passed to it.

YOu can use the same variable name in two subs. They only have meaning in the sub they are in.

Sub Testxyz2()
Dim rng As Range
Set rng = Range("D4").Offset(0, -3)
xyz rng
End Sub


Sub Testxzy3()

For 1 = 1 to 3
For j = 1 to 2
xyz Cells(i, j)
Next j
Next i

End Sub

Never use a VBA or an Excel Keyword as a variable name. Cel for Cell, rng for Range, WkBk for Workbook, Sht for Sheet. Etc.

[vijyat]

Awesome. :thumb