• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 471
  • Last Modified:

Excel Macro User Form to imput parameters

I have a macro that works and is working fine.  This is the code.

Sub DeleteZeros()
Dim ws As Worksheet
Dim I As Long, FinalRow As Long

For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    

FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For I = FinalRow To 12 Step -1
    If (Cells(I, 19).Value = "") Then GoTo lastline
        If (Cells(I, 19).Value = 0#) And (Cells(I, 23).Value = 0#) And (Cells(I, 27).Value = 0#) Then

'Rows(I).Interior.ColorIndex = 2
Rows(I).Delete Shift:=xlUp
        End If
lastline:
    Next I
    
    Next ws

End Sub

Open in new window


What it does, is searches a worksheet for zeros, and if all 3 have zeros it deletes the line, and skips null lines to preserve formatting.  And runs the process through all of the worksheets in a workbook.   this works great however the end user has a number of report to generate with this code with varying column numbers and counts.

User Form
I created this user form to handle the input.  Here is what I need to do.

work this out so the form launches, and the user can choose how many columns to search (1,2 or3), what those columns are (Ideally by column letter, and I am not sure how to convert that yet) and what and what to search for.  

I would like to set default values to the original macro.  3 columns selected, those values and to search for 0#.

And I have no idea how to make this work.  :(

I may even be doing this the wrong way, I think I need to pass the parameters from the form to the macro using variables.  I am not sure how to make it understand the column count, or how to add additional AND statements with each check box to add or remove how many columns.  

And I am not sure how to convert the column number to the column letter.

I have done things like this in Access before and it does not seem to work quite the same way and I am a little lost.

Thanks for your help.
0
larspanky
Asked:
larspanky
  • 4
1 Solution
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

And I am not sure how to convert the column number to the column letter.

You will probably find every "Expert" can propose a different method.

Here is just one...

To display the alphanumeric column reference for column number 12:

MsgBox Split(Columns(12).Address(ColumnAbsolute:=False), ":")(0)

BFN,

fp.
0
 
larspankyAuthor Commented:
I will give that a try.  Thank you.

Any thoughts on the column selection, or how I can call only parts of the macro with the form?

Or would it be better to write the macro into the form?
0
 
larspankyAuthor Commented:
The customer changed the scope slightly so I now only need to include one column.  

The user would input the one column they want to search, by column letter from A-ZZ and I would prefer to allow a variable for what they are searching for but the requirement is only zeros.  

I am open to any method, I can scrap the user form if its easier to use a MSGbox.  If you can help me get that done, I can close this and award points.

How would you do it?
0
 
larspankyAuthor Commented:
This is the code I ended up delivering.  They were happy with only selecting one column.

The change produces an input box and processes the macro based on whatever column you select.  Works Great.

Sub DeleteZeros_SingleColumn()
Dim ws As Worksheet
Dim I As Long, FinalRow As Long
Dim x As Range
'Converts selected column to integer
Dim y As Integer
On Error Resume Next
'Select Cells in Column to process
Set x = ActiveCell
'of selection select whole column
y = x.Column
'input box to select column with mouse
Set x = Application.InputBox("Select the column to Search using the mouse", Type:=8)
For Each ws In ActiveWorkbook.Worksheets
ws.Activate
    
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    For I = FinalRow To 12 Step -1
'Skip null value rows
    If (Cells(I, y).Value = "") Then GoTo lastline
        If (Cells(I, y).Value = 0#) Then
Rows(I).Delete Shift:=xlUp
        End If
lastline:
    Next I
'Scroll to next worksheet
    Next ws

End Sub

Open in new window

0
 
larspankyAuthor Commented:
I worked it out on my own.
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now