Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA: FIND FIRST ROW

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


Post your answer or comment

comments powered by Disqus
I've searched through the forums and the internet and I'm not finding what I need.
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).

Hello,

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:
	
 
 
 
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 Sub 

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

	VB:
	
 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
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'm creating a macro which will automatically create a graph based on a range of dates entered by the user into two cells before clicking a button.

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

Need VBA code to find first blank row on worksheet.

I have a bunch of spreadsheets where I want to

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
bbowler@bigelow.org |
+-------------------+---------------------------------------------------+

i need to do 3things using excel VBA
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

Hello all. I'm trying to implement a formula across all 0'ed rows in a number of workbooks I have saved in a folder. The macro should copy the formula that is currently in F2:V2, and copy it to all 0ed out rows in the workbook (of course, maintaining formula relativism, i.e., what was A2 in the formula should become A9 or whatever).

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 Sub
I 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.

Hi,

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 Sub
Thanks in advance!

Hello,

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 Sub
If 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

Hi,
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.

Hello all,

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

I am looking for a way to find the row number of the first empty row within a range.

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?

Hi,

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

Hello,
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

I have a macro that takes data from one sheet and adds it into a table on another form, at the moment it all works fine as long as all fields are filled in everytime a new entry is added. What I want is to be able to have some fields blank, to do this I need to be able to find the last row with data in any column on the first sheet, and the first row with no data in any columns on the next sheet. Is there a simple way this can be done.
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

I have two columns of numbers:

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?

i am trying to write a macro to find the text "total" in the first row and first column. and this macro works fine for first row:

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 Sub
i 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.

When I do a sort with a macro key, I always get a number of blank rows above the first row with any data. I would like to automatically determine how many rows there are until my first row of data and then delete these rows automatically by editing the VBA code of my sort macro Sub.

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.

Hi!

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 Sub
Thank you!

/Martin

Hi all,

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

Hello,

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.

I'm attepting to find the row number of the first row in a user selected
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

How can I find the value of the first row of the same column of the active cell? I am using this bit of code, which works fine if there are no blank rows in between the active cell and the first row of data:
I tried using this line instead but now just getting an error message.
	

	


Hi guys,

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.