Hi,

FINDING THE FIRST ROW FOR "KVV"

When I do a filter to the Segment Code for "KVV" , the first row for "KVV" is Row 4.

I have done a Marco to help me find the FIRST ROW of "KVV" but the marco will not go and find the FIRST ROW of "KVV" it will just find the Second Row.

I want the marco help me find the first row of "KVV", that is Row 4.

How am I going to do that??

see attract file to have a clearer view.

THANK YOUâ€¦

Code:

Sub Macro1()

'

'

column1 = Range("A1").End(xlToRight).Column

Range(Cells(1, 1), Cells(1, column1)).Select

Selection.AutoFilter

Selection.AutoFilter Field:=5, Criteria1:="KVV"

'it will not offset to the fourth row of "KVV"

'it will select two row, WHY?

Cells(1, 5).Offset(1, 0).Select

firstrow = ActiveCell.Row

firstcolumn = ActiveCell.Column

End Sub

FINDING THE FIRST ROW FOR "KVV"

When I do a filter to the Segment Code for "KVV" , the first row for "KVV" is Row 4.

I have done a Marco to help me find the FIRST ROW of "KVV" but the marco will not go and find the FIRST ROW of "KVV" it will just find the Second Row.

I want the marco help me find the first row of "KVV", that is Row 4.

How am I going to do that??

see attract file to have a clearer view.

THANK YOUâ€¦

Code:

Sub Macro1()

'

'

column1 = Range("A1").End(xlToRight).Column

Range(Cells(1, 1), Cells(1, column1)).Select

Selection.AutoFilter

Selection.AutoFilter Field:=5, Criteria1:="KVV"

'it will not offset to the fourth row of "KVV"

'it will select two row, WHY?

Cells(1, 5).Offset(1, 0).Select

firstrow = ActiveCell.Row

firstcolumn = ActiveCell.Column

End Sub

- Find first row of filtered data
- How to Find First Row After Autofilter for variable columns
- VBA - Finding first matching date in column & returning row number
- FIND FIRST BLANK ROW IN A WORKBOOK
- Finding the number of rows and the first row with a specific value
- Finding a row with border in VBA
- Finding first 0ed row in VBA
- Open, find first empty row, paste, save, close!!!
- How to Find First Row After Autofilter for variable columns
- VBA code to freeze only the first row in all the sheets.
- VBA: finding the row number of a selection
- Find first Empty Row Number
- VB to find first occuring number & then sum range of cel
- First row in Selection range (first index of a cell) EXCEL VBA
- Find first empty row in a worksheet with a Macro
- Finding the Row Number with the First Paired Set
- VBA: find text in rows & columns
- Find the row number of the first cell entry in a column
- Find first empty cell in a row and insert date and time
- Find First Row and Last Row
- Find Replace data into multiple cells from the first row
- Finding the row number of first row in a selected range
- Find the value in the first row of same column of active cell
- Find first empty row in a specific range

I need to place the cursor into the first row of a filtered list, column A.

For example, I have a list from row 6 through row 4500, columns A through AB

I've written the macro to set the autofilter on and apply my selection criteria.

At this point, in this example, the first row of filtered data (below the header row) is in row 3083. I need to move the cursor to cell A3083 and then type in a formula.

Can anyone provide the code to move to the first row of the filtered data (not the header row).

I currently have a dataset setup like this:

Site x y1 y2 y3 1 11 1 6 1.5 1 20 4 546 34 1 45 7 3 345 1 67 9 12 778 2 123 12 32 34 2 154 2 123 67 3 132 4 12 12 3 34 4 56 2 3 23 1 34 123

I made up the numbers, but the format is the same.

What I want to do is find the slope of the line for y1,2,3 vs. x - FOR EACH SITE and paste these values into another sheet. Therefore site 1 will have 3 slopes on the next worksheet: y1 vs x, y2 vs x, and y3 vs x.

My current approach is to apply an autofilter to the first column to look at only one site at a time, then calculate the slopes using a loop. The autofilter is also looped to go through each site. However I am running into an error: I can't get VBA to see the correct ranges for the x's and y's after I autofilter. Here is my code so far:

VB:Specifically the issue is atSub Slopes() Dim i As Integer Dim j As Integer Dim Yrng As Range Dim Xrng As Range Dim v As Range i = 0 j = 0 For i = 0 To (Sheet2.UsedRange.Rows.Count - 3) 'Autofilter Loop, Sheet 2 has a list of all the sites With Sheet1 Application.ScreenUpdating = False .AutoFilterMode = False .Range("A1:J1").AutoFilter .Range("A1:J1").AutoFilter Field:=1, Criteria1:=Sheet2.Cells(3 + i, 1) 'Autofilters for the site it reads on Sheet 2 End With j = 0 For j = 0 To (Sheet1.UsedRange.Columns.Count - 4) 'Slope loop, should calculate the slope for each y and paste to Sheet 2 Set Yrng = Sheet1.Range(Cells(2, j + 4), Sheet1.Cells(Range("a" & Rows.Count).End(xlUp).Row, j + 4)) 'Having trouble here with making the ranges variable with each autofilter Set Xrng = Sheet1.Range(Cells(2, j + 3), Sheet1.Cells(Range("a" & Rows.Count).End(xlUp).Row, j + 3)) Sheet2.Cells(3 + i, 2 + j) = Application.WorksheetFunction.Slope(Yrng, Xrng) Next j Next i End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:because after it goes to the next autofilter, it still reads from the ACTUAL row 2, not the VISIBLE row 2 in the filter.Yrng = Sheet1.Range(Cells(2,j+4)If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Can anyone help?

Thank you,

Jake

The dates are in column B and are sorted so that the later dates are lower down the column. There are multiple rows withc each date as there is a separate column with times next to it.

The start and end dates are entered into two cells on the sheet "options" which has data verification to ensure it is a vaild date.

The value for start & end date is then assigned a to a variable start_date & end_date.

I've been playing about with the find function but not sure exactly how to code it so it does what I want.

I need to be able to search down the way in column B starting at row 3 and find the first match for start_date. I need it to return this row number as a variable (start_row as long).

I then also need to return the LAST row which contains the end date. Not sure if I'm best starting at the bottom here and so finding the first match up the way or going down and finding the last match.

I could do this using a loop so that my loop counter ends up giving me the row number, but as there are a huge number of rows I'm sure there is a better way to do it.

Any help is much appreciated.

cheers

Dave

sort the entire sheet on column A and then column B (that part I can

handle)

The I want to find the first row in column A that has a 1 in it and move

everything from that row thru the last row into a new sheet in the same

workbook.

I know how to do it all manually, but since there are a bunch of these

files I'd like to do it in VBA. The part I'm not quite sure how to do

efficiently (I know how to do it inefficiently) is find the row number of

the last row and the row number of the first row that has a 1 in column A.

Any help and/or pointers would be appreciated...

Excel 2003 if it matters.

Thanks!

Bruce

--

+-------------------+---------------------------------------------------+

Bruce Bowler | A fox should not be of the jury at a goose's trial.

1.207.633.9600 | - Thomas Fuller

[email protected] |

+-------------------+---------------------------------------------------+

1. finding the first row in a worksheet which have a border.

2. find the first cell which have contant.

3. is there a way to copy only partial cell contant?

thank in advanced

dana

This is the macro I am trying to make work (found on an excel site via google, then edited to suit my needs):

Option Explicit Sub UpdateFormulas() 'JBeaucaire 3/18/2010 'Open all xls files in a folder, update the formulas IN E:G on every sheet Dim fPath As String, fName As String, LR As Long Dim NewFormulaRNG As Range, ws As Worksheet Application.DisplayAlerts = False Application.ScreenUpdating = False 'The range of cells that will serve as the master formulas Set NewFormulaRNG = ThisWorkbook.ActiveSheet.Range("F2:V2") 'Path to stored files fPath = "C:2010" 'create a list of files in that folder fName = Dir(fPath & "*.csv") 'Loop through files one at a time Do While Len(fName) > 0 'open the file Workbooks.Open fPath & fName 'Copy in the formulas into each sheet For Each ws In ActiveWorkbook.Worksheets LR = ws.Range("A" & ws.Rows.Count).End(xlUp).Row Application.CutCopyMode = False ws.Range("$A$1:$AO$" & LR).AutoFilter Field:=6, Criteria1:="0" NewFormulaRNG.Copy ws.Range("F2:V2") ws.Range("F2:V" & LR).Select Application.CutCopyMode = False Selection.FillDown Calculate Next ws 'Save and close the updated file ActiveWorkbook.Close True 'ready the next filename fName = Dir Loop Set NewFormulaRNG = Nothing Application.DisplayAlerts = True Application.ScreenUpdating = True End SubI have two questions:

1. How do you make it so that, instead of copying the formula to the F2:V2 in each workbook, it copies it to the first row with 0s in F:V? In some cases, this might be row 2, in many cases, it won't be.

2. When it fills down with Autofilter enabled, will the VBA skip the filtered out rows like Excel does normally?

Attached is an example of the kind of files I'm running this on.

Thanks for the help.

How could I do all those steps in title in VBA? I found some examples, but they doesn't work. I can open other workbook, but nothing more happens.. I can't perform functions in other workbook (select sheet, find first empty row..) How could I do that? My code is:

Private Sub OpenListFile() Dim pathxls As String Dim fileName As String Dim strFile As String Dim target As String Dim rFound As Range Dim i As Range pathxls = "R:HelpdeskMarius" fileName = "SCE template incl lists v7.xls" strFile = pathxls & fileName Set oExcel = New Excel.Application Set oWB = oExcel.Workbooks.Open(strFile) oExcel.Visible = True 'Application.ScreenUpdating = False ActiveWorkbook.Sheets("Parts").Select Set i = Sheets("Parts").Range("A1:A1500") target = "" On Error Resume Next Set rFound = i.find(what:=target) On Error GoTo 0 'rFound.Select rFound.Value = txtArt.Value rFound.Offset(0, 1).Value = txtDescr.Value 'Workbooks(fileName).Close SaveChanges:=True Set oExcel = Nothing Set oWB = Nothing Application.ScreenUpdating = True End SubThanks in advance!

I currently have a dataset setup like this:

Site x y1 y2 y3

1 11 1 6 1.5

1 20 4 546 34

1 45 7 3 345

1 67 9 12 778

2 123 12 32 34

2 154 2 123 67

3 132 4 12 12

3 34 4 56 2

3 23 1 34 123

I made up the numbers, but the format is the same.

What I want to do is find the slope of the line for y1,2,3 vs. x - FOR EACH SITE and paste these values into another sheet. Therefore site 1 will have 3 slopes on the next worksheet: y1 vs x, y2 vs x, and y3 vs x.

My current approach is to apply an autofilter to the first column to look at only one site at a time, then calculate the slopes using a loop. The autofilter is also looped to go through each site. However I am running into an error: I can't get VBA to see the correct ranges for the x's and y's after I autofilter. Here is my code so far:

VB:

Option Explicit Sub Slopes() Dim i As Integer Dim j As Integer Dim Yrng As Range Dim Xrng As Range Dim v As Range i = 0 j = 0 For i = 0 To (Sheet2.UsedRange.Rows.Count - 3) 'Autofilter Loop, Sheet 2 has a list of all the sites With Sheet1 Application.ScreenUpdating = False .AutoFilterMode = False .Range("A1:J1").AutoFilter .Range("A1:J1").AutoFilter Field:=1, Criteria1:=Sheet2.Cells(3 + i, 1) 'Autofilters for the site it reads on Sheet 2 End With j = 0 For j = 0 To (Sheet1.UsedRange.Columns.Count - 4) 'Slope loop, should calculate the slope for each y and paste to Sheet 2 Set Yrng = Sheet1.Range(Cells(2, j + 4), Sheet1.Cells(Range("a" & Rows.Count).End(xlUp).Row, j + 4)) 'Having trouble here with making the ranges variable with each autofilter Set Xrng = Sheet1.Range(Cells(2, j + 3), Sheet1.Cells(Range("a" & Rows.Count).End(xlUp).Row, j + 3)) Sheet2.Cells(3 + i, 2 + j) = Application.WorksheetFunction.Slope(Yrng, Xrng) Next j Next i End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Specifically the issue is at

VB:

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

because after it goes to the next autofilter, it still reads from the ACTUAL row 2, not the VISIBLE row 2 in the filter.

Can anyone help?

Thank you,

Jake

I am looking for a VBA code that will freeze all the first row in all the sheets in the workbook.

For example when I press CTRL+Q , all the first rows in the sheets qill be freeze.

It will be a great help.

Thanks in advance,

Fatalcore.

I am creating a report from a vba program.

The report is to have bold headings.

Headings will appear throughout the report varying according to what the program illuminates.

I'm using a 'find last used cell, offset' method of gettingf the cell and filling it. I then want to make it bold.

i understand I can write

selection.font.fontstyle = "bold"

but what I reeeeeeally would like to accomplish is to embolden the whole ROW of the selection so I can turn all columns bold in one go rather than repeating the instruction for each offset.

Any ideas? Does it make sense even?!

Many thanks,

Zazie

In other words I want to look in column B starting at Row 24 and find the first empty cell (col 2, row x). I to need return the X into a variable in the macro. Any suggestions/examples?

I've posted this in another question but someone suggested a VBA Approch to best get what I want so this is a re-post.

Basicly I want it to sum a given number of consecutive rows of values in column J, the first row being where the first populated cell appears in column J (as opposed to a formula)

The number of rows to sum would be in the cell u17. So if u17 = 4, then it would sum only the first four consectuive populated cells in column J, the start being where the first populated cell with a number appears.

I've started in trying to determine the start of the range to sum using the code below but this approach does not work because the code finds the first cell with a formula in it and not number. Is there any way to get it to find a populated number instead? I'm assuming I need to use some worksheet function like isnumber but I'm just guessing at this point. Then I was thinking I could use an offset function to define the range to sum.

Sub test()

Sheets("Jamma").Range("J1").End(xlDown).Select

End Sub

Hope someone can help.

Thanks,

Greedy

I have a problem with selecting first cel in selection Range or return

an index of the first cell in Selection Cell.

I have something like this (VBA Code):

....................

Range1.Select

"and here I want to Select the first range in selection Range1"

.............

I there any special function of finding first cell in selection range

or returning an index of the first cell??

Thanks for answet

Marcin

At the moment I use this:

Range(Selection, Selection.End(xlToRight)).Select

Range(Selection, Selection.End(xlDown)).Select

but this will find the first empty fields and so doesn't necesarily find all the data.

Thanks for any assistance.

J

A B

1

4

2

6

4

5 6

3 3

2 4

4 1

What is an easy way to find the row number that contains the first entry of two numbers. Obviously, the answer is 6 but what is the formula?

Sub test() Dim r As Long Dim c As Range Dim sFirst As String With Rows(1) Set c = .Find("TOTAL", LookIn:=xlValues) If Not c Is Nothing Then sFirst = c.Address Do c.Value = "" Set c = .FindNext(c) Loop While Not c Is Nothing End If End With End Subi want the above macro to find "total" anywhere in the first column and anywhere in the first row. cells(r,c)

thanks again for your response.

I have be trying something like row(3,cell(value,$a$1)) and then deleting these rows where $a$1 is where I am trying to calculate where that first data row is, but it is not working consistantly.

My sort range will also give me a large number of blank rows after the rows of data are sorted together which is adding to the complexity for me.

Any ideas would be greatly appreciated.

I've made a macro that selects an entire row from one sheet and inserts into first empty row on another sheet. After I've done this I would like the first empry cell in the "new row" to contain the date and time of creation, but I can't seem to figure this out.

This is the code I'm currently using:

Sub Macro1() ' ' Macro1 Macro ' Rows(ActiveCell.Row).Select Selection.Copy Sheets("Sheet2").Select ActiveCell.Offset(1, 0).Select Do While Not IsEmpty(ActiveCell) ActiveCell.Offset(1, 0).Select Loop ActiveSheet.Paste ' This is where I want to find first empty cell in active row and insert current date and time Sheets("Sheet1").Select Application.CutCopyMode = False End SubThank you!

/Martin

I'm trying to improve on some code I'm using to import row data from other spreadsheets. The only data I'm interested in importing are the rows where

Column A contain a numerical value. The start and end range for the numerical values will vary from sheet to sheet.

I've provided a couple of demo sheets with code that supposed to

show the first/start row and and last/end row. The code works fine

in one workbook but not the other, though the sheet data is setup the same.

I need some code which will find the start row and end row with more accuracy.

I've searched the web but haven't found anything yet that works in my case.

Any help is appreciated.

Thanks,

BDB

Excel gives the option to find and replace information...but does it have the capability to replace the information from multiple cells in a row to the colums below it???????

I have been tasked with making some information readable for inport into a firm programmed application. the information is stored excel and will be copied from csv. At the moment some information is not written out. I.E. there is an X in the cells that represent the information that is in the first row above it. Quite typical for tracking purposes.

I would like to do a find replace for all x's so that they represent the exact data in the first row. Basically the x in the cells below the first row should be the text rather than an x representing it.

Your help is greatly appreciated,

Michael

Row--> [08] Versicherung [09] Vertrieb [10] Eingang/Ausgang

replace

this into

the ones

below

[08] Versicherung [09] Vertrieb X

[09] Vertrieb X

[09] Vertrieb X

X

X

X

X

With find and replace I am doing one col at a time.

range.

I'm looping throgh the rows to perform a comparison and can establish the

total number of row using

Dim Lrows as integer

Lrows = Selection.Rows.Count

How do I extract the row number of the first row, please.

Thanks in advance

I tried using this line instead but now just getting an error message.

I have this code to use with a form and add lines in range A12:J25 Sheet1, problem is that it is giving me an error in the section highlighted in red. The problem is that I need the information from the form to be populated in rows 12 thru 25, each time I add data, it needs to find the first empty row beetween 11 and 25 and place the data there without over writing the prior data. Can anyone help me?

Private Sub cmdAdd_Click()

Dim iRow As Long

Dim ws As Range

Set ws = Range("A12")

'find first empty row in database

iRow = ws.Cells(Rows.Count, 1) _

.End(xlUp).Offset(1, 0).Row

'copy the data to the database

ws.Cells(iRow, 1).Value = Me.ComboBox1.Value

ws.Cells(iRow, 2).Value = Me.ComboBox2.Value

ws.Cells(iRow, 6).Value = Me.TextBox3.Value

ws.Cells(iRow, 7).Value = Me.TextBox1.Value

ws.Cells(iRow, 8).Value = Me.TextBox2.Value

'clear the data

Me.ComboBox1.Value = ""

Me.ComboBox2.Value = ""

Me.TextBox3.Value = ""

Me.TextBox1.Value = ""

Me.TextBox2.Value = ""

Me.ComboBox2.SetFocus

End Sub

No luck finding an answer? You could always try Google.