Delete blank rows

[dhivya.enjoy]

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.




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(xlCellTy peBlanks).EntireRow.Delete

'wbkNew.Sheets(1).UsedRange.SpecialCells(xlCellTyp eBlanks).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]

'...........................
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]

Hi

Add these lines before you save the workbook as CSV.


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

[dhivya.enjoy]

Hi

Add these lines before you save the workbook as CSV.


On Error Resume Next
wbkNew.Sheets(1).Columns(1).SpecialCells(4).Entire Row.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

[patel]

attach please the excel file that generates the csv file

[Admin]

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.