Macros for delete entire row in excel 2007

P

pnnaik

New member
Joined
Apr 14, 2015
Messages
9
Reaction score
0
Points
0
I am attaching excel file.I want following using excel macros 2007.
1) selection of sheet2
2) Sheet has data in only one column "A"
3) Entire range of data (data in column A only) to be selected using macro
4) selection of black row in data base
5) deleting blank row.
6) I have seen that when macros run it slow down the process. If any trick please guide.

kINDLY HELP.

Regards,
PN
 

Attachments

  • QUE.xlsx
    36.8 KB · Views: 25
In your attached file, Sheet2, column A..... the first blank cell is at row 2611.
All the cells above that, appearing to be blank, actually contain a single space character.
The delete key should be used to empty a cell, not the space bar.

Maybe a macro along the lines of this ?
Code: 
Sub DeleteBlankRows()
    Dim LastRow As Long
    Dim i As Long

Application.ScreenUpdating = False
With Sheets("Sheet2")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
    For i = LastRow To 1 Step -1
        If Trim(.Cells(i, 1).Value) = "" Then .Rows(i).EntireRow.Delete
    Next i
End With
Application.ScreenUpdating = True
End Sub
 
Hi pnnaik

If I understand (not certain I do), try this
Code: 
Option Explicit
Sub Delete_Blank_Rows()
   Dim ws           As Worksheet
   Dim LR           As Long
   Application.ScreenUpdating = False
   Set ws = Sheets("Sheet2")
   With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="="
      .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 
Both Macros are working. Thanks for your kind reply.
If I want to delete line with special character like ============================================================================ and
----------------------------------------------------------
How to accomodate this in above macros. Kindly suggest.
Regards,
PN

 
Hi pnnaik

Try this
Code: 
Option Explicit
Sub Delete_Blank_Rows()
   Dim ws           As Worksheet
   Dim LR           As Long
   Application.ScreenUpdating = False
   Set ws = Sheets("Sheet2")
   With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="="
      .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:= _
                                     "=*-----*", Operator:=xlOr, Criteria2:= _
                                     "=*======*"
      .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      .AutoFilterMode = False
   End With
End Sub
 
Thanks John.
It is working. I will get back to you for help after final requirements.
Regards,
PN
 
I used both macros.
But it takes more times when it has to process more than 25000 cells in column A.
Also some time also sometimes machine get hanged. I am using excel-2007.

I need following

MACRO-1

1) I want to trim all cells
2) Want to delete blank cell, special character ======= ,and ------

MACRO-2

In addition to MACRO-1
I want to keep only those cell which content

"C O L U M N"
"Fe*** (Main)" (here *** will take any numeric value)
"CROSS SECTION"
"% exceeds"
"GUIDING"
"REQD. STEEL AREA"
"REQUIRED STEEL AREA"

I know very less about VBA but I used to with excel function.
Regards,
PN
 
Hi PN

See if this Code in the attached does as you require. CTRL + x will fire the Code.
Code: 
Option Explicit
Sub Delete_Blank_Rows()
   Dim ws           As Worksheet
   Dim LR           As Long
   Dim TrimRng      As Range
   Application.ScreenUpdating = False
   Set ws = Sheets("Sheet1")
   With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:="="
      .Range("A2:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      .Range("A1:A" & LR).AutoFilter Field:=1, Criteria1:= _
                                     "=*-----*", Operator:=xlOr, Criteria2:= _
                                     "=*======*"
      .Range("A1:A" & LR).SpecialCells(xlCellTypeVisible).EntireRow.Delete
      .AutoFilterMode = False
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      Set TrimRng = .Range("A1:A" & LR)
      Call TrimRange(TrimRng)
      Set TrimRng = Nothing
      Call test
   End With
End Sub


Sub TrimRange(ByRef MyRng As Range)
   Dim vY   'as Variant
   With MyRng
      vY = Evaluate("IF(ROW(" & .Address & "),TRIM(" & .Address & "))")
   End With
   MyRng.Resize(UBound(vY, 1), 1).Value = vY
End Sub


Sub test()
   Dim ws           As Worksheet
   Dim DataRng      As Range
   Dim CritRng      As Range
   Dim LR           As Long
   Dim LC           As Long
   Application.ScreenUpdating = False
   Set ws = Sheets("Sheet1")
   With ws
      LR = .Cells.Find("*", .Cells(.Rows.Count, .Columns.Count), SearchOrder:=xlByRows, _
                       SearchDirection:=xlPrevious).Row
      LC = .Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByColumns, _
                       SearchDirection:=xlPrevious).Column + 1
      Set DataRng = .Range("A1:A" & LR)
      Set CritRng = .Cells(1, LC).Resize(2)
      Cells(2, LC).Formula = "=AND(ISNA(MATCH({""C O L U M N N O. "",""*Fe*(Main)*"",""*GUIDING* "",""*CROSS SECTION:* "",""*REQD. STEEL "",""*exceeds maximum limit*"",""** REQUIRED STEEL*""}&""*"",A2,0)))"
      With DataRng
         .AdvancedFilter xlFilterInPlace, CriteriaRange:=CritRng
         .Offset(1, 0).EntireRow.Delete
      End With
      .ShowAllData
      CritRng.EntireColumn.Delete
   End With
   Application.ScreenUpdating = True
End Sub
 

Attachments

  • QUE v1.xlsm
    68.3 KB · Views: 10
John,

This is what I want. Many thanks.
Now I straight little more. With this data I want to apply formula in excel. As per QWE v1 file data is upto A305.

1) Suppose I want to insert formula in column B,C,D.

My problem is that If I put formula for up to B50000, c50000, D50000 CELL EXCEL take lot time to open as well for save the file. I have take upto 50000 as a example. Data may be up to 50 cells or more than 50000 cells.

I want formula activate in cell B,C,D as per last used cell in A column. In our example case it is A305.

Thanks again.
Regards,
PN
 
Hi PN

This is beyond the scope of your Thread.

Please determine what your end goal is then start a new Thread with all the specifications spelled out.
 
Dear John,

Thanks for kind reply and help. I will create new thread and provide reference of this thread.
Regards,
PN
 
You must log in or register to reply here.
Back
Top