Delete selective rows and move specific cells

Folks,
In the attached file I have two columns labeled Product and Zip Code. I would like to be able to have a command button that deletes all blank rows when the value in a Zip Code cell is blank. The next think I need is to have an input box that allows me to select which zip code cells I want to move over one column. For example, in the input box I may want every 10th zip code moved for this file moved over 1 cell. For other files it may be every 5th. This will become a template and data will be copied and pasted into it.
Delet-and-Move.xlsx
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
This is the result of entering 5 into the Inputbox.
28794899.xlsm
0
Frank FreeseAuthor Commented:
Martin,
OK...However, just got the actual file I'll be using and the user has a couple of changes (as well as a few problems I just found out)
1. He needs only column E, ZIP Code. Deleting the other columns is not a problem for me, except in column E there are some lookups. There I need to convert all cells form E2 to the end of column E formatted as number. The I can delete columns A:D.
2. I tried modifying your code and could not get the rows to delete. So I need help there.
3. I'll submit a different question where the user would have a Icon of the QuickTool bar so the code is executed once.
MoveandCopy.xlsm
0
Martin LissOlder than dirtCommented:
I'm not sure I understand but try this.

Sub DeleteAndMove()
Dim intFrequency As Integer
Dim lngLastRow As Long
Dim lngRow As Long

lngLastRow = Range("E1048576").End(xlUp).Row

Application.ScreenUpdating = False

Columns("E:E").Select
Selection.AutoFilter
ActiveSheet.Range("E:E").AutoFilter Field:=1, Criteria1:="="
ActiveSheet.Range("E1:E" & lngLastRow).Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Selection.AutoFilter

lngLastRow = Range("E1048576").End(xlUp).Row

intFrequency = InputBox("Please enter the number rows to skip when moving Zip Codes")

If intFrequency = 0 Then Exit Sub

For lngRow = intFrequency To lngLastRow
    If lngRow Mod intFrequency = 0 Then
        Cells(lngRow + 1, "F") = Cells(lngRow + 1, "E")
        Cells(lngRow + 1, "E").ClearContents
    End If
Next

Application.ScreenUpdating = True
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Frank FreeseAuthor Commented:
Thanks Martin - I so tired I'll look at this tomorrow
0
Frank FreeseAuthor Commented:
Perfect! - now let me see what else my client wants. It is good to be back working.
As always, I appreciate you. Thanking you kindly
0
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you
including these two new ones.
Creating your own Excel Formulas and doing the impossible
A Guide to Writing Understandable and Maintainable VBA Code
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
1
Frank FreeseAuthor Commented:
Thank you for the update
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.