Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find current row number and select row
I am very new to Excel VB and I need some help. With some other macros, I end
up with several rows with "N/A" in some of the colums. I am able to sort the spreadsheet and get the rows to end up at the top of the spreadsheet, then I am trying to delete those rows. So I need to find the row number that has data, then move up one row and delete the rows from there to one from the top. Below is what I have so far. Sub DeleteBlankNARecords() Dim RowNum As Integer Sheets("PlDetails").Select Cells.Select ActiveWorkbook.Worksheets("PlDetails").AutoFilter. Sort.SortFields.Clear ActiveWorkbook.Worksheets("PlDetails").AutoFilter. Sort.SortFields.Add Key:= _ Range("$D$1:$D$896"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _ :=xlSortNormal With ActiveWorkbook.Worksheets("PlDetails").AutoFilter. Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A1").Select Selection.End(xlDown).Select ' set row number to current row Rows("RowNum:RowNum").Select Range(Selection, Selection.End(xlUp)).Select Rows("2:RowNum-1").Select Range("ARowNum").Activate Selection.ClearContents Selection.Delete Shift:=xlUp Range("A2").Select End Sub Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find current row number and select row
Replace the below lines with
Selection.End(xlDown).Select ' set row number to current row Rows("RowNum:RowNum").Select RowNum = Range("A1").End(xlDown).Row Rows(RowNum).Select If this post helps click Yes --------------- Jacob Skaria "JohnM" wrote: I am very new to Excel VB and I need some help. With some other macros, I end up with several rows with "N/A" in some of the colums. I am able to sort the spreadsheet and get the rows to end up at the top of the spreadsheet, then I am trying to delete those rows. So I need to find the row number that has data, then move up one row and delete the rows from there to one from the top. Below is what I have so far. Sub DeleteBlankNARecords() Dim RowNum As Integer Sheets("PlDetails").Select Cells.Select ActiveWorkbook.Worksheets("PlDetails").AutoFilter. Sort.SortFields.Clear ActiveWorkbook.Worksheets("PlDetails").AutoFilter. Sort.SortFields.Add Key:= _ Range("$D$1:$D$896"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _ :=xlSortNormal With ActiveWorkbook.Worksheets("PlDetails").AutoFilter. Sort .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Range("A1").Select Selection.End(xlDown).Select ' set row number to current row Rows("RowNum:RowNum").Select Range(Selection, Selection.End(xlUp)).Select Rows("2:RowNum-1").Select Range("ARowNum").Activate Selection.ClearContents Selection.Delete Shift:=xlUp Range("A2").Select End Sub Thanks |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find current row number and select row
You may not need to do any sorting, try this on an experimental worksheet: Sub DeleteBlankNARecords() Dim RowNum As Integer Sheets("PlDetails").Select For RowNum = 896 To 2 Step -1 If Application.WorksheetFunction.IsNA(Cells(RowNum, "D")) Then Rows(RowNum).Delete 'perhaps change the line above to whatever is in column D that you want to delete eg.: 'If Cells(RowNum, "D").Value = "N/A" Or Cells(RowNum, "D").Value = "" Then Rows(RowNum).Delete 'you get the drift Next RowNum End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125286 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find current row number and select row
This worked for me.
Thanks! -- JohnM "p45cal" wrote: You may not need to do any sorting, try this on an experimental worksheet: Sub DeleteBlankNARecords() Dim RowNum As Integer Sheets("PlDetails").Select For RowNum = 896 To 2 Step -1 If Application.WorksheetFunction.IsNA(Cells(RowNum, "D")) Then Rows(RowNum).Delete 'perhaps change the line above to whatever is in column D that you want to delete eg.: 'If Cells(RowNum, "D").Value = "N/A" Or Cells(RowNum, "D").Value = "" Then Rows(RowNum).Delete 'you get the drift Next RowNum End Sub -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=125286 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current year and current week number | Excel Discussion (Misc queries) | |||
Detect if current select is a row | Excel Programming | |||
how to select current sheet tab | Excel Programming | |||
Select Current Row | Excel Programming | |||
find highest number then select next cell | Excel Programming |