VBA Question - Copy/Paste Special Skip Blank Rows - Excel 2010

[Johnny Thunder]

Hello all,

I am working on a project and hit a road block.

I have a spreadsheet that users will populate using various dropdown lists and such. Data begins on B17:M67, a user could even add in additional rows if needed using a macro.

My specific question is, if a user decided to enter data in the middle of the spreadhsheet instead of starting on the first row how would you code a Copy/Paste to not include the blank rows of data when pasting to another sheet?

I have a macro that copies the users data and converts it to a CSV file for an upload template and I can't have blanks in the file so I need a way to have the blanks skipped and all data starting from the top like if a user started populating from the first row B17.

Please let me know if I am explaining this clearly. Thanks for all the help in advance.

'First Copy License Fee Calculator
  
  With ThisWorkbook.Sheets("License Fee Calculator")
    .Range("B17:L17", .Cells(.Rows.Count, "B").End(xlUp)).Copy
        
  End With
  
  Sheets("CSV Export").Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                   SkipBlanks:=True, Transpose:=False
 
  Application.CutCopyMode = False
  
  'Copy Qty, Subtotal & Grand Total - License Fee Calculator
  With ThisWorkbook.Sheets("License Fee Calculator")
    .Range("M17:O17", .Cells(.Rows.Count, "M").End(xlUp)).Copy
        
  End With
  
  Sheets("CSV Export").Range("M2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                   SkipBlanks:=True, Transpose:=False
 
  Application.CutCopyMode = False
  
  'Copy Agreement Type - License Fee Calculator
  With ThisWorkbook.Sheets("License Fee Calculator")
    .Range("P17", .Cells(.Rows.Count, "P").End(xlUp)).Copy
        
  End With
  
  Sheets("CSV Export").Range("V2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
                                                   SkipBlanks:=True, Transpose:=False
 
  Application.CutCopyMode = False

 

[Jerry Sullivan]

Hi Johnny Thunder,

There's different ways to do this. Please clarify a few details and I'll try to suggest some code that will be a good fit.

1. Are there headers in Row 16 that exactly match the headers in the corresponding destination fields in Row 1 of CSV Export?

2. Approximately how many rows of data will be copied? (1-100), (100-1000), (1000-10,000), (10,000+)

3. Do you want to keep the rows of data aligned between the 3 sets of ranges copied if one set has a row of all blanks, but another set has a value in that row? For example if P17 is blank and M17 is not, should the desired result in CSV Export!V2 be the blank from P17 or the value in P18 moved up?

4. Are the blanks completely empty cells or formula that return a null string ""?

 

[Johnny Thunder]

Hi Johnny Thunder,

There's different ways to do this. Please clarify a few details and I'll try to suggest some code that will be a good fit.

1. Are there headers in Row 16 that exactly match the headers in the corresponding destination fields in Row 1 of CSV Export?

2. Approximately how many rows of data will be copied? (1-100), (100-1000), (1000-10,000), (10,000+)

3. Do you want to keep the rows of data aligned between the 3 sets of ranges copied if one set has a row of all blanks, but another set has a value in that row? For example if P17 is blank and M17 is not, should the desired result in CSV Export!V2 be the blank from P17 or the value in P18 moved up?

4. Are the blanks completely empty cells or formula that return a null string ""?

Spent all night working on this and was able to find a work around solution. Thank you for your help with this.