Delete blank rows based on user selection

Question

I have a spreadsheet that essentially has two sets of data from two different years that I put into one spreadsheet to compare and line up by adding rows to either set of data. I have a macro already that compares the cells that I need and insert rows in order to line up the data, sometimes it misses something and a blank row is added into one of the data sets that shouldn't be there which then throws everything after it out of whack. So then I have to fix the alignment in the area, delete all the blank rows in the corresponding data set after the misalignment and re-run my macro hoping it doesn't mess up again.

So to speed up the blank row deleting part I was trying to make a macro to do this for me. For example, I have data from 2014 in columns A through DC and 2010 data from DE to EG. I was trying to create another macro that would allow me to select a range of cells such as DE69:EG69 and it would go through each row in the same column range DE70:EG70, DE71:EG71 etc and if all the cells in the range is blank, delete the range and shift the data up. Everything I try and find online just seems to delete the entire row rather than just the range I select.

I even tried a few ways that said it would work by selecting the range and running the macro but that didn't seem to work either.

So I tried to record the macro and then make the needed edits but I'm still having issues. I have tried using some code from my other macro to allow an input box to pop up and put the column letters where the begin and end of the data set is but so far its not working.

 Option Explicit
 Sub delee()
 Dim colX As String
 Dim colX2 As String
 colX = UCase(InputBox("Enter the left column", "Compare Columns", "A"))
 colX2 = UCase(InputBox("Enter the last column", "Compare Columns", "E"))
     Range(colX & "1000000:" & colX2 & "1000000").Select
     Selection.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
 End Sub

Answer 1

Give this Code a shot

Dim BlankHits as Long
Dim NormalHits as Long
For Each Target in Selection
NormalHits = NormalHits + 1
    If Target.Value = "" Then
        BlankHits = BlankHits + 1
    End If
    If BlankHits = NormalHits Then
        Selection.Delete Shift:= xlUp
    End If
Next Target

This currently searches through the cells you have selected. If you want it to search through a different range then change where it says selection to whatever range you desire. Hope this helps.

Answer 2

Do the following:

"Record Macro"

Highlight 3 or 4 cells on a single row.

Right click, "Delete", choose "shift rows up".

Click OK.

Then click "Stop recording".

Go in and view your macro.

I ended up with this:

Sub Macro1()
'
' Macro1 Macro
'

'
    Range("A5:B5").Select
    Selection.Delete Shift:=xlUp
End Sub

So that shows you the "basics" .. you need to do 2 things:

1) "Select" your range you want to delete. (sounds like you can build this, since you'll know what row you want, and you know what columns, so just build the string, and: `Range(yourstring).select`

2) tell it to delete + shift up .. 

Hope that helps .. :)

Answer 3

So just in case anyone stumbles across this when searching, below is what I used to get what I needed. Essentially, an input box will come up and ask for the first cell in the selection and the last cell in the selection, filter out blanks in the first column of selection, select and delete the blank rows. This can take awhile depending on the size of your data but it works. Thanks for the two that offered advice and help!

Sub deleteblanks()

 Dim colX As String
 Dim zzz As String
 Dim lastRow As Long


 lastRow = ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count

 MsgBox lastRow

 colX = UCase(InputBox("Enter The Start Of Data To Be Filtered", "Delete Blanks", "A"))
 zzz = UCase(InputBox("Enter The End of Data To Be Filtered(Last Column Last Row)", "Delete Blanks", "AM65500"))

Range(colX & ":" & zzz).Select
Selection.AutoFilter
ActiveSheet.Range(colX & ":" & zzz).AutoFilter Field:=1, Criteria1:="="
ActiveWindow.SmallScroll Down:=-3
Range(colX & ":" & zzz).Select
Selection.SpecialCells(xlCellTypeVisible).Select
ActiveSheet.ShowAllData
Selection.Delete Shift:=xlUp
 End Sub