Delete blank rows

**dhivya.enjoy** · 10-23-2013, 03:23 PM

Hi Team,

Attached is my csv file and there are empty rows without any values, i wanted to remove such rows.

Kinldy provide me a code.

Below code is the use which i used to populate data in csv from an excel sheet.

Code:

strThirdCSVName = "Third"
    strThirdTable = "RATE_GEO_COST_GROUP"
    
    ThirdTable_Col1 = "RATE_GEO_GID"         ' Naming the columns of the RATE_GEO_COST_GROUP CSV
    ThirdTable_Col2 = "RATE_GEO_COST_GROUP_GID"
    ThirdTable_Col3 = "RATE_GEO_COST_GROUP_XID"
    ThirdTable_Col4 = "RATE_GEO_COST_GROUP_SEQ"
    ThirdTable_Col5 = "MULTI_RATES_RULE"
    ThirdTable_Col6 = "RATE_GROUP_TYPE"
    ThirdTable_Col7 = "DOMAIN_NAME"


    
    
     strCSV_3_Columns = "B" ' Let Say Columns D,F,H and Columns J to L
    
    
    
    wbkNew.Sheets(1).Cells.Clear
        strRange = MakeRange(strCSV_3_Columns)
        .Range(strRange).Copy
        wbkNew.Sheets(1).Range("A1").PasteSpecial xlPasteValuesAndNumberFormats
        wbkNew.Sheets(1).Rows(1).Insert
      '  wbkNew.Sheets(1).Rows(1).Insert
        wbkNew.Sheets(1).Cells(1).Value = strThirdTable
        
        
            wbkNew.Sheets(1).Rows(2).Cells(1).Value = ThirdTable_Col1 'This line code will add additional column to csv
            wbkNew.Sheets(1).Rows(2).Cells(2).Value = ThirdTable_Col2
            wbkNew.Sheets(1).Rows(2).Cells(3).Value = ThirdTable_Col3
            wbkNew.Sheets(1).Rows(2).Cells(4).Value = ThirdTable_Col4
            wbkNew.Sheets(1).Rows(2).Cells(5).Value = ThirdTable_Col5
            wbkNew.Sheets(1).Rows(2).Cells(6).Value = ThirdTable_Col6
            wbkNew.Sheets(1).Rows(2).Cells(7).Value = ThirdTable_Col7
            'wbkNew.Sheets(1).Rows(2).Cells(3).Value = FirstTable_Col3
            'wbkNew.Sheets(1).Rows(2).Cells(4).Value = FirstTable_Col4
        
    

Dim j As Integer
'Dim i As Integer
i = 3
k = 3
 lastcell = Range("A" & Cells.Rows.Count).End(xlUp).Row
 For j = 2 To lastcell
    Rate_basis1 = UCase(wksAct.Cells(j, 16).Value)
    RR_name = UCase(wksAct.Cells(j, 2).Value)
    
     If Not (Rate_basis1 = "") Then
     
                       'wksAct.Cells(j, 16).Select
                        'wksAct.Cells(j, 16).Activate
                                  ' MsgBox RR_name
                                  ' MsgBox RR_basis1
                                   'End If
                                   
                                   
                wbkNew.Sheets(1).Range("B3").Activate
                
                 'For i = 3 To wbkNew.Sheets(1).Range("A1").End(xlDown).Row - 0
                 wbkNew.Sheets(1).Rows(i).Cells(1).Value = "GRK." & RR_name
                wbkNew.Sheets(1).Rows(i).Cells(2).Value = "GRK." & RR_name
                wbkNew.Sheets(1).Rows(i).Cells(3).Value = RR_name
                
                'col1 = wbkNew.Sheets(1).Rows(i).Cells(1).Value
                'col2 = wbkNew.Sheets(1).Rows(i).Cells(2).Value
                             'wbkNew.Sheets(1).Rows(i).Cells(2).Value = "GRK." & wbkNew.Sheets(1).Rows(i).Cells(1).Value
                             
              
              End If
          
       
          

                ' Next i
                i = i + 1
               ' MsgBox i
             '  wbkNew.Sheets(1).Range("A1").Activate
                         
                             If (wbkNew.Sheets(1).Rows(k).Cells(2).Value) = "" Then
      '    MsgBox k & "empty"
            wbkNew.Sheets(1).Rows(k).Cells(1).Value = ""
        End If
         k = k + 1
                   
'wbkNew.Sheets(1).Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
                   
'wbkNew.Sheets(1).UsedRange.SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
   ' Range("A3:A10").Select
   
    
 wbkNew.Sheets(1).Range("A1").Activate
    
Next j



wbkNew.Sheets(1).Range("D3").Activate
                 For i = 1 To wbkNew.Sheets(1).Range("A1").End(xlDown).Row - 2
                ActiveCell.Value = "A"
                ActiveCell.Offset(1).Activate
                Next i
                wbkNew.Sheets(1).Range("A1").Activate
                

  

        
        Application.DisplayAlerts = False
        wbkNew.SaveAs Filename:=strSaveLocation & strThirdCSVName, FileFormat:=xlCSV, CreateBackup:=False
        Application.DisplayAlerts = True
        
        wbkNew.Close 0

Please advise.

**patel** · 10-23-2013, 05:20 PM

Code:

'...........................
wbkNew.Sheets(1).Range("D3").Activate
 For i = 1 To wbkNew.Sheets(1).Range("A1").End(xlDown).Row - 2
                ActiveCell.Value = "A"
                ActiveCell.Offset(1).Activate
 Next i
 wbkNew.Sheets(1).Range("A1").Activate
 Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete (xlShiftUp)
 Application.DisplayAlerts = False
 wbkNew.SaveAs Filename:=strSaveLocation & strThirdCSVName, FileFormat:=xlCSV, CreateBackup:=False
Application.DisplayAlerts = True
 wbkNew.Close 0

**Admin** · 10-23-2013, 05:26 PM

Hi

Add these lines before you save the workbook as CSV.

Code:

On Error Resume Next
wbkNew.Sheets(1).Columns(1).SpecialCells(4).EntireRow.Delete
On Error GoTo 0

**dhivya.enjoy** · 10-23-2013, 07:19 PM

Originally Posted by Admin

Hi

Add these lines before you save the workbook as CSV.

Code:

On Error Resume Next
wbkNew.Sheets(1).Columns(1).SpecialCells(4).EntireRow.Delete
On Error GoTo 0

Hi,

This will not delete my blank rows in first row right? i want them as blank only since my first rwo of csv will hold table name. and second row will have all column name and 3rd row will have data.

Regards
Dhivya

**Admin** · 10-23-2013, 10:04 PM

Originally Posted by dhivya.enjoy

Hi,

This will not delete my blank rows in first row right? i want them as blank only since my first rwo of csv will hold table name. and second row will have all column name and 3rd row will have data.

Regards
Dhivya

The code would delete all those rows where there is no data in Column A.