Insert Row and move On Problem

travelingwilly

New Member
Joined
Jun 19, 2002
Messages
24
I am trying to read a sorted Sheet by the first column and if it is missing a value of One (1) before values in column 1 changes I wish to Insert an Entire Blank row with a Partial Copy the essentials so someone can Add the Missing info. Problem is How do I Select A Row ....

Public Sub chkfirst()
Dim fndlvl1 As Boolean

'Sort Authorizers
Sheets("Authorizers").Activate
ActiveWorkbook.Names.Add _
Name:="WORKA", _
RefersTo:=Range("a2", Range("a2").End(xlDown))

Selection.Sort Key1:=Range("a2"), Order1:=xlAscending, Key2:=Range("c2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom

'Load Authorizers
y = ""
With Sheets("Authorizers").Range("WORKA")
For s = 1 To .Count
If CStr(.Cells(s, 1)) <> y Then
chng = False
If fndlvl1 = False Then
Rows(" cstr(s + 1) + ":" + cstr( s + 1) ").Select
Selection.Insert Shift:=xlDown
.Cells(s, 1) = .Cells(s - 1, 1)
.Cells(s, 2) = .Cells(s - 1, 2)
.Cells(s, 3) = "**No LVL#1**"
fndlvl1 = False
chng = True
End If
y = CStr(.Cells(s, 1))
End If
If chng = False And .Cells(s, 7) = 1 Then
fndlvl1 = True
End If
chng = False
Next s
End With
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

JohnG

Board Regular
Joined
Feb 18, 2002
Messages
165
try using Rows option

dim RowLtr as string

sub test
RowLtr="A"

range(Rows(RowLtr & ":" _ RowLtr).Address).select
 

travelingwilly

New Member
Joined
Jun 19, 2002
Messages
24
Problem is I do not know what the row is

this is in error
Rows(" cstr(s + 1) + ":" + cstr( s + 1) ").Select

This is what I get when I record macro...
Rows("7:7").Select
Selection.Insert Shift:=xlDown

is there a way to figure out what row I am on and replace the "7:7"
 

travelingwilly

New Member
Joined
Jun 19, 2002
Messages
24
Problem is I do not know what the row is

this is in error
Rows(" cstr(s + 1) + ":" + cstr( s + 1) ").Select

This is what I get when I record macro...
Rows("7:7").Select
Selection.Insert Shift:=xlDown

is there a way to figure out what row I am on and replace the "7:7"
 

Forum statistics

Threads
1,144,766
Messages
5,726,173
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top