Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Blank Cells then deleting those rows
Ryan responded with this code to highlight the cells. What code would delete
the rows? ------------------------ So you just want to "Select" the cell? Use this, Sub Highlighter() Dim lngLastRow As Long Dim rng As Range lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row For Each rng In Range("D1:D" & lngLastRow) If IsEmpty(rng) Then rng.Select Exit For End If Next rng End Sub -- Cheers, Ryan -- Mickey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Blank Cells then deleting those rows
Try this
Sub Highlighter() Dim lngLastRow As Long Dim rng As Long LastRow = Cells(Rows.Count, "D").End(xlUp).Row For rng = LastRow To 1 Step -1 If IsEmpty(Cells(rng, 4)) Then Cells(rng, 4).EntireRow.Delete End If Next rng End Sub Mike "Mikey" wrote: Ryan responded with this code to highlight the cells. What code would delete the rows? ------------------------ So you just want to "Select" the cell? Use this, Sub Highlighter() Dim lngLastRow As Long Dim rng As Range lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row For Each rng In Range("D1:D" & lngLastRow) If IsEmpty(rng) Then rng.Select Exit For End If Next rng End Sub -- Cheers, Ryan -- Mickey |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Blank Cells then deleting those rows
You would have to modify the code to a For...Next Loop instead of a For Each
Loop. I wrote two different ways to delete empty rows. You can decide which you prefer. OPTIOIN 1: Sub DeleteRows1() Dim lngFirstRow As Long Dim lngLastRow As Long Dim i As Long lngFirstRow = 1 lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row For i = lngLastRow To lngFirstRow Step -1 If Cells(i, "D") = "" Then Rows(i).Delete SHift:=xlUp End If Next i End Sub OPTION 2: Sub DeleteRows2() Dim lngFirstRow As Long Dim lngLastRow As Long lngFirstRow = 1 lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row With Sheets("Sheet1") .Range(.Cells(lngFirstRow, "D"), .Cells(lngLastRow, "D")). _ SpecialCells(xlCellTypeBlanks).EntireRow.Delete SHift:=xlUp End With End Sub Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Mikey" wrote: Ryan responded with this code to highlight the cells. What code would delete the rows? ------------------------ So you just want to "Select" the cell? Use this, Sub Highlighter() Dim lngLastRow As Long Dim rng As Range lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row For Each rng In Range("D1:D" & lngLastRow) If IsEmpty(rng) Then rng.Select Exit For End If Next rng End Sub -- Cheers, Ryan -- Mickey |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Blank Cells then deleting those rows
Perhaps:
Sub Highlighter() Dim lngLastRow As Long Dim rng As Range, rDelete As Range lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row For Each rng In Range("D1:D" & lngLastRow) If IsEmpty(rng) Then If rDelete Is Nothing Then Set rDelete = rng Else Set rDelete = Union(rDelete, rng) End If End If Next rng If rDelete Is Nothing Then Else rDelete.EntireRow.Delete End If End Sub -- Gary''s Student - gsnu200909 "Mikey" wrote: Ryan responded with this code to highlight the cells. What code would delete the rows? ------------------------ So you just want to "Select" the cell? Use this, Sub Highlighter() Dim lngLastRow As Long Dim rng As Range lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row For Each rng In Range("D1:D" & lngLastRow) If IsEmpty(rng) Then rng.Select Exit For End If Next rng End Sub -- Cheers, Ryan -- Mickey |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Blank Cells then deleting those rows
Assuming your cells in Column D have **data** in them so that your =""
condition means an empty cell, and **not formulas** that evaluate to "", then give this "non looping" macro a try... Sub DeleteEmptyCellsColumnC() On Error Resume Next Columns("D").SpecialCells(xlCellTypeBlanks).Entire Row.Delete End Sub -- Rick (MVP - Excel) "Mikey" wrote in message ... Ryan responded with this code to highlight the cells. What code would delete the rows? ------------------------ So you just want to "Select" the cell? Use this, Sub Highlighter() Dim lngLastRow As Long Dim rng As Range lngLastRow = Cells(Rows.Count, "D").End(xlUp).Row For Each rng In Range("D1:D" & lngLastRow) If IsEmpty(rng) Then rng.Select Exit For End If Next rng End Sub -- Cheers, Ryan -- Mickey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting rows when certain cells are blank | Excel Programming | |||
deleting rows with blank cells after a specified column? | Excel Discussion (Misc queries) | |||
Deleting blank (Cells/Rows) in Excel-VBA | Excel Programming | |||
Deleting rows with blank cells | Excel Programming | |||
Deleting rows with blank cells | Excel Worksheet Functions |