Ignore blank rows

Hi,
Can someone please advise how I can input an if statement on vba that if a row contains no values or a spreadsheet contains no information that the code will ignore row. Here is the code I've gotten to combine information from multiple spread sheets but just need to put in an error statement or if statement in. Not sure which is needed

Thanks
Seamus

Sub Combine()
'
Dim NumSheets As Integer
Dim NumRows As Integer

' Change the value of NumSheets to equal the number of sheets you wish to combine

NumSheets = 10
' Change the value of NumRows to equal the number of rows in each sheet

NumRows = 500
Worksheets(12).Select
Sheets.Add
ActiveSheet.Name = "Consolidated"
For X = 1 To NumSheets
Worksheets(X + 1).Select
Rows("2:" & NumRows).Select
Selection.Copy
Worksheets("Consolidated").Select
ActiveSheet.Paste
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
Worksheets(X + 2).Select
Range("A1").Select
Next X
Worksheets("Consolidated").Select
Range("A1").Select
End Sub

Open in new window

Seamus2626Asked:
Who is Participating?
 
AgeOfEmpiresConnect With a Mentor Commented:
There are several approaches, IMO, but the optimal solution is dependent on the nature of your data.

The hardcoded 500 lines stands out as a potential troublemaker (as does the hard coded number of worksheets).  What happens if (when?) the data extends beyond 500 rows, there is a new user running the routine, and they forget to edit the code?  You get an inaccurate consolidated worksheet.

Therefore, I'd go about this a little differently, which would also accomplish ignoring blank rows on the source worksheets.

Essentially, for each source sheet, I'd loop through all the rows in the UsedRange of the worksheet (based upon how the spreadsheet has been maintained, this may involve WAY more rows than contain actual data, but adapting some of the following logic, you could build in a maintenance plan to get rid of any extra rows at the end).  While looping through the rows, for each row, check to see if A1 is blank.  If not, you need to copy the row.  If A1 is blank, do an end(xlToRight), and check that cell to see if it is blank.  If your entire row is blank, it will be.  If there is data any where in your row, the cell at end(xlToRight) will not be blank and you know you need to copy that row.

Now, as mentioned, this requires you to loop through each row on each source sheet, but given that your above parameters show 10 worksheets and 500 rows each, this methodology should not pose a real performance problem.  If you've got formulas in any of the cells, you could turn off auto calc, do all of the copying, and then reenable calc (assuming it was on when you started the copy).
0
 
David L. HansenProgrammer AnalystCommented:
What is the symptom you are experiencing (error, slow down, bad results)?
0
 
Seamus2626Author Commented:
To be honest im not really sure why my colleague has requested this. I think what may be suitable is if i set the range on both work books using ColA and then using that delete any blank rows inbetween and then copy on to the new workbook? Have you that code to Set the range and then delete blank rows inbetween?

Thanks
Seamus
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Glenn RayConnect With a Mentor Excel VBA DeveloperCommented:
Couple of observations:
1) If NumRows is intended to be the actual number of rows copies, you need to add one to any subsequent formulas using it because the source sheets are copied from row 2 to NumRows (line 17), leaving you one row short.
2) If a blank row exists in the source data, then the Selection.End(xlDown).Select statement (line 21) will not work because it will only move the active cell down as far as the first blank row in the copied data on the Consolidated worksheet.

So, am I correct in the assumption that if a blank row(s) exists in the source data sheets, those rows don't count toward the NumRows total?  For example, if there are 10 blank rows, you actually want to copy as far as the 510th row (eventually removing the blank rows from the copy process)?
0
 
ragnarok89Connect With a Mentor Commented:
Seamus,

Assuming you are using 5 columns per row
Assuming a blank row is one where all 5 columns are blank

Load the entire range into an array. Then for each row, check if any columns are blank. If yes, move to the next row; if not then paste the row:
Sub Combine()
    
Dim NumSheets As Integer
Dim NumRows As Integer

' Change the value of NumSheets to equal the number of sheets you wish to combine

NumSheets = 10
' Change the value of NumCols to equal the number of columns in each sheet
NumCols = 5
' Change the value of NumRows to equal the number of rows in each sheet
NumRows = 500
Worksheets(12).Select
Sheets.Add
ActiveSheet.Name = "Consolidated"
For x = 1 To NumSheets
    Worksheets(x + 1).Select
    'put all rows into an array, this assumes Col E is last column
    temparray = Range("A2:E" & NumRows)
    Worksheets("Consolidated").Select
    'select last used row, and then the one below
    Range("A65536").End(xlUp).Offset(1, 0).Select
    'check each column of each row in the array
    For i = 1 To UBound(temparray)
        For j = 1 To NumCols
            'if a blank is found, goto next row in array
            If temparray(i, j) = "" Then GoTo blank:
        Next j
        'if no blanks, paste the row
        For j = 1 To NumCols
            ActiveCell.Offset(1 - i, 1 - j).Value = temparray(i, j)
        Next j
blank:
    Next i
    Worksheets(x + 2).Select
    Range("A1").Select
Next x
Worksheets("Consolidated").Select
Range("A1").Select
End Sub

Open in new window

0
 
Seamus2626Author Commented:
This question was a favour for a friend who wouldnt provide the data behind it, so im going to close it off

Thanks for all your help
0
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.

All Courses

From novice to tech pro — start learning today.