Free Microsoft Excel 2013 Quick Reference

Vba Code To Count Rows

Hi,

I’m looking for a VBA code which counts the number of rows which contains data.
I was looking in the forum and found this thread.

Unfortunately the code is not returning the correct number of rows
I was playing a bit around but never got the correct result

	VB:
	
 countRows() 
    Dim lngRowsProject As Long 
    lngRowsProject = Columns("AG:AG").SpecialCells(xlVisible).Rows.Count ' returns me 21 (column AH has 21 rows, but column AG has just 2)
    lngRowsProject = Range("AG").CurrentRegion.Rows.Count ' returns me 43 (the total numbers of rows in the spreadsheet are 43)
    MsgBox "The number of rows is " & lngRowsProject 
End Sub 

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

Does anyone know how to change the code that it’s just returning the number of rows in column AG???

Thanks a lot in advance!

Martin


Post your answer or comment

comments powered by Disqus
I am trying to create VBA code to count the number of cells within a range that have either text or numbers entered in them. This needs to work on a row by row basis, i.e. the same code applied to row 6, 7, 8 onwards.

I have 12 cells in a row range that are being written into the spreadsheet using another code.

The activecell is set as a cell in column B

The 12 cells (labelled 'Deployments') are found in columns AL:AW and are being written using the codes

to

How do I create a countif code that is able to reference these values?

Can post a sample worksheet if it helps. Alternatively, the worksheet can be found under a previous thread

http://www.excelforum.com/excel-prog...d-working.html

Thanks

USD $10 by paypal vba code to delete rows in workbooks

Hi again brilliant forum

i am an excel novice

had some great work done by Wigi and Krishnu in the past

I have about 120 excel files (each has about 1 million rows and between 6 and 30 Columns).

the files are 200mb + so too big to upload

i want a vba macro to delete unwanted rows

is it possible to have a message or input box that i can enter up to 5 words and if any of these words are contained in any rows then i wish to keep these rows only (and if any of these words dont appear i wish to delete the whole row)

the macro will be deleting about 950 thousand rows and keeping about 50 thousand (hopefully).

i can do it very slowly with custom sort but if a macro can be done it will make life easier

Good luck if you fancy the challenge

Paul

Hello

I have referred to a previous discussion called "VBA Code to delete rows which contain a certain character" but whilst this has helped, I could do with a bit more guidance if possible.

A sample of the data I'm working with looks like this (in column C):

Microsoft Office XP Standard
Security Update for Windows XP (KB927802)
Windows XP Hotfix - KB918439
Windows Installer 3.1 (KB893803)
Shockwave
Squirrel SQL v2.1
Update for Windows XP (KB911280)
Screensaver
etc...

I would like to find out the best way of deleting all the rows that contain the following text:
"hotfix"
"security update"
"windows installer"
"update for windows XP"

I was thinking along the lines of the suggestion offered in the "VBA Code to delete rows which contain a certain character" - to use autofilter but even using macros, I am struggling to turn this into code.

Any help would be much appreciated.
Thanks
Grace

Hey,

Thanks in advance for any help. I have a workbook in which I have multiple worksheets which are dependant on one input page. I have vba code to hide and unhide blank rows in one worksheet. What I would like to do is be able to hide corresponding rows in another worksheet. I have run into 2 problems. First, I can write code to check for blank rows, but I want it to update on every enter keystroke and this cycles through 2000 lines and takes too long. I would like to have it execute only when a cell changes. Next, the rows contain the same data, but are arranged in a different order so that row 4 on one worksheet is not the same as row 4 on the other worksheet. Any suggestions?

thanks,

Mike

Hello I am trying to get the VBA code to delete a row if there is no data in a cell in that row. for example if D5 is blank delete row 5.

I can get the Code to do this function row by row but I dont want to write it out 5000 times. Is there a formula to say repete function between rows 18-5000

i have a large spreadsheet with certain rows i need to delete if they have certain strings of text. i need coding to delete rows only if they contain any of the various certain strings of text as a whole. i found code that deletes rows if any single word is found, but i need it to find only exact words together (e.g. county of residence, - not just if any of those words are found elsewhere like when "of" is found in "zip code of residence"

so: i need to delete rows that contain "county or residence" or "age of respondent" or "zip code of employment"

I also need it to search 2 different columns rather than just one.

(i think macros would be the easiest solution??)
any help would be appreciated! thanks!

Can anyone let me know the code to add rows on a button click.
Thnx

Hey all i want to have a code that does the following ... If column B multiply by C is not equal to zero Don't Delete OR Column D multiply by Column E is not equal to zero Don't Delete Else Delete. That's as much as i could work on and i'm confused about how to do an or function using VBA that's why i wrote two different code to do the same thing which didn't work out too good.

Code :
------------------------

Cells.Select
With Selection
Dim a, lastcell, rowno, totalR
lastcell = Range("a65536").End(xlUp).Address
Range("a1:" & lastcell).Select
rowno = Selection.Rows.Count + 1
Range("a1").Select
For a = 1 To rowno - 1
If WorksheetFunction.Product(ActiveCell.Offset(0, 3), ActiveCell.Offset(0, 4))

Hi All,
Can anyone help me convert the below VBA code to AppleScript? I use a Mac Powerbook that has the Microsoft Excel 2008 application installed since few days and have no access to the Excel 2004 for Mac anymore where I used this VBA code.

Sub KillDupes()
Dim rCell As Range
Dim rRange As Range
Dim lCount As Long

Set rRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
lCount = rRange.Rows.Count

For lCount = lCount To 1 Step -1
With rRange.Cells(lCount, 1)
If WorksheetFunction. CountIf(rRange, .Value) > 1 Then
.Clear
End If
End With
Next lCount
End Sub

Thank you in advance for your assistance.

Anybody have the VBA code to determine the last row in an imported Excel file so I know how many times to insert or copy-down a formula which I add in a new column? Note: the added formula begins on row-2 of new column (because heading of new column is in row-1).

The number of lines/rows in imported file is always different.

Know this is a common issue, but I'm still learning how to search this site.

Thanks in advance for any help.

Howdy all,

I need some VBA code to count the number of rows in a column that have data.

I want to use this number as a count in a for-next loop. I've got the for-next loop worked out, but I'm currently setting the count manually - not dynamicaly.

Do I need to set up a single-column dynamic Named Range and find the last row or is there an easier way to do this with VBA?

Hi
I want to write a vba code in "Sheet2", to select rows from "Sheet1" based
on my condition and then paste those rows in defined range in "Sheet2"
Example
I want to select rows in sheet1 where the condition is that cell value
c3=d3, I have 500 rows. Now the rows that meet the condition, I want them to
be displayed in sheet 2 in the cell range starting from a20 to a2000
Please advise

Somthing like one of these may work...

a.
Worksheets("Sheet1").Range("A18"). _
AutoFormat Format:=xlRangeAutoFormatClassic1

b.
Columns("E:E").EntireColumn.AutoFit

You will have to play with these, and the Range/Columns as
required.

>-----Original Message-----
>VBA code changes the Excel column/row size, Can we write
>VBA code to set all column/row's size back to default
size?
>
>Thanks in advance!
>.
>

Within a macro I have a "found" cell (Find "US03" in certain column) which
can be on any row. I need code to count down 170 rows from this cell and
insert 2 rows. This is done from the top of the worksheet.

Thanks!!
Valerie

Hello All,

Pretty new to the VBA coding so i need some help with a process i would like to automate using macro/VBA code in excel. What I need to do is copy an entire row from Workbook "Config Log" where Column A is like 01-0001-0000 and Column B is like "ALTERNATOR" and Column E is not NULL then Paste into Workbook "Template" Row A1.

So I wrote/recorded a Macro to copy the exact cells that I need however the Workbook "Config Log" has row inserts daily and that messes up my macro.

The process I would like to automate is inserting new information onto the template then renaming the template. My biggest issue is the the workbook "Config Log changes daily so i need to search by specific key works or part numbers everytime i run the macro from the template. It would be best if the macro could be ran from the template. Any assistance would be greatly appreciated, thanks in advance.

Hi Guys,

Please I need a vba code to find Invoice No. in a database and replace content of a cell in the same row.

I have a Sales database with 7 columns, as follows:

1. Invoice No. - Column A
2. Invoice Date - Column B
3. Month of Sale - Column C
4. Gross Amount - Column D
5, Vat Amount - Column E
6. Net Amount - Column F
7. Date Paid - Column G

I have created a Multipage Excel Userform for - (1). Sales Data Entry; and (2).Receipts Entry.

I already have a vba code for the Sales Data Entry, and the code works fine. When an invoice is generated, the Sales Data Entry part of the Multipage Userform is used to enter the sales invoice details onto the database. At this stage, what is entered in the last column i.e. Column G is the word 'UNPAID'.

The second page of the Multipage Userform is to be used to enter Receipts and update the database. That page contains only two textboxes, namely - (1) Invoice No. and (2) Date Paid, and a command button named 'Enter Receipt'.

Please I need a macro to run the page 2 of multipage userform (i.e. the 'Receipts' page) - so that when a user enters the Invoice No. and Date Paid, and clicks the 'Enter Receipts' button, the macro will make a search in Column A of the database for the Invoice No. that has been paid. If the Invoice No. is found, then replace the word 'UNPAID' that is entered against the invoice no. in Column G with the Date Paid (in dd/mm/yyyy format).

I need to clarify that each Invoice No. is unique and is never repeated in Column A (i.e. an invoice number cannot appear more than once in column A). The vba code should also be able to provide a message saying 'Invoice No. not found' if the search does not find an invoice no. that is keyed into the 'Receipts' userform (may be in error).

Thanks in advance for your kind help.

Buddy

Can someone suggest simple (I'm a beginner) VBA code to identify the last
used row in a particular sheet? Then, once the last used row is identified,
use that variable to extend formulas on different sheets from row A2 to make
the used row number?

I'm working with a spreadsheet that is so large, I'm trying to fit the
needed rows with the input data which changes constantly.

Thanks!

Hi all,

Apologies for making my first post on here a request for help - I have been trying to tweak a VBA code to get the result I am after, however failing miserably.

I've got an Excel file that I am using to calculate some approximations for anisotropic material data for use with FEA. All of the data lies on Sheet1. The data that I want to export starts on row 30, and ends on row 33.

The actual cells that I want exporting are:

A30
A31-H31
A32-H32
A33-E33

I need the output seperated by commas. My current code is below (taken off the net):

Public Sub ExportToTextFile(FName As String, _
    Sep As String, SelectionOnly As Boolean, _
    AppendData As Boolean)

Dim WholeLine As String
Dim FNum As Integer
Dim RowNdx As Long
Dim ColNdx As Integer
Dim StartRow As Long
Dim EndRow As Long
Dim StartCol As Integer
Dim EndCol As Integer
Dim CellValue As String


Application.ScreenUpdating = False
On Error GoTo EndMacro:
FNum = FreeFile

If SelectionOnly = True Then
    With Selection
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
Else
    With ActiveSheet.UsedRange
        StartRow = .Cells(1).Row
        StartCol = .Cells(1).Column
        EndRow = .Cells(.Cells.Count).Row
        EndCol = .Cells(.Cells.Count).Column
    End With
End If

If AppendData = True Then
    Open FName For Append Access Write As #FNum
Else
    Open FName For Output Access Write As #FNum
End If

For RowNdx = StartRow To EndRow
    WholeLine = ""
    For ColNdx = StartCol To EndCol
        If Cells(RowNdx, ColNdx).Value = "" Then
            CellValue = Chr(34) & Chr(34)
        Else
           CellValue = Cells(RowNdx, ColNdx).Value
        End If
        WholeLine = WholeLine & CellValue & Sep
    Next ColNdx
    WholeLine = Left(WholeLine, Len(WholeLine) - Len(Sep))
    Print #FNum, WholeLine
Next RowNdx

EndMacro:
On Error GoTo 0
Application.ScreenUpdating = True
Close #FNum
End Sub


Sub DoTheExport()
    Dim FileName As Variant
    Dim Sep As String
    FileName = Application.GetSaveAsFilename(InitialFileName:=vbNullString, FileFilter:="Text Files
(*.txt),*.txt")
    If FileName = False Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Sep = Application.InputBox("Enter a separator character.", Type:=2)
    If Sep = vbNullString Then
        ''''''''''''''''''''''''''
        ' user cancelled, get out
        ''''''''''''''''''''''''''
        Exit Sub
    End If
    Debug.Print "FileName: " & FileName, "Separator: " & Sep
    ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
       SelectionOnly:=False, AppendData:=True
End Sub
Current problems:

1. Exports the whole sheet, including data above/below the section I want to export.
2. Exports blank cells (ie. B30-H31 and G33-H33) and prints ", , ," to .txt file.
3. I dont really want to offer the user the option of choosing a seperator - Ideally I would like it to be fixed as a comma but still offer the option of choosing file name and location.

The text file needs to be exported in the same 'order' as the cells are on the sheet (ie. A30, <new line>A31-H31,<new line>A32-H32,<New line>,A32-F32.)

Any help would be much appreciated!

Kind regards

Tom

Does anyone know the VBA code to add a row at the bottom of a table and enter the values from a userform into column row C onwards and automatically continue the numbering down from Column B to the row (done in the format of 1, 1.1, 2, 2.2, 2.3 etc)?

Thanks

Hello forum.
I am going to have 3 files in a folder. One file is the macro which I was hoping to get codes to run. The other file is the csv file which I need the macro to work on. The third file is a text file which is a source of reference.
In the CSV File, first sheet, Column D (starting from row 2 because row 1 is the header), there are names of students.
Example in cell D2, it is “13 - Thomas Rich” and in Cell D5, it is “11 - Michelle Newton” and in cell D4, it is “09 - Cindy Bell”
These are all names of students and it goes from cell D2 to the last row.

My source of reference is the text file (Sample name list.txt) in this folder. This file will indicate the word, “Female” after a commar on the name.
Example, “02 – Jennifer Crown,Female”
On the next line, it may show “86 – Tom Hardy” (the male names will not be indicated that this is a male name)
Only female name such as “98 – Mary Brown,Female” will be indicted in this text file.

Is there a way to code a VBA into the macro so that it will use the source file and identify the female names and extract the whole row (I only need from column A to Q) into a separate new excel file? I need a new excel file for the female names (with header) and another file for the male students
Thank you.

I hope I am not making anyone confused. Thank you so much!

All

My spreadsheet has about 12 columns of data that is entered by hand. Each row of data is values given by a participant in a course for a particular element of the course (such as Overview, Handouts, etc). A participant may not attend a particular section of a course - thus those cells will be empty. I want to be able to count to number of scores entered and ignore the blank cells. I'm doing this through VBA as it feeds into a larger calculation for averages.

I have figured out how to find the last cell in a row:
But I can't seem to figure out how to count only the cells in the row that have a value.

I have looked in the forums - but am not sure what I should be looking for.

Thanks
Didge.

I have set up my spreadsheet and wriiten vba coding to save to the data sheet with the following details (see below) and this work perfect.....however what i also require is for this to be saved as well into indiviual tabs that have been set up (tab name is the VRN for each vehicle) approx 100+ tabs. Is there a simple addition to the vba code that will allow the user form input to write to the main data sheet and the relevant vehicle sheet?

eRow = wsData.Range("A" &
Rows.Count).End(xlUp).Offset(1, 0).Row

With wsData
.Cells(eRow, 1) = cboWkNo.Text
.Cells(eRow, 2) = TxtDateFill.Text
.Cells(eRow, 3) = cboDepot.Text
.Cells(eRow, 4) = txtDriverName.Text
.Cells(eRow, 5) = txtVRN.Text
.Cells(eRow, 6) = txtMakeModel.Text
.Cells(eRow, 7) = txtFuel.Text
.Cells(eRow, 8) = CheckBox1.Value
.Cells(eRow, 9) = CheckBox2.Value
.Cells(eRow, 10) = txtOdo.Text


I have an array A1:F1500 is important to find if any of the rows are repeated, I attache a little example, I don't want use excel functions I want a vba code to do this task, I am using excel 2003. any executable file welcome. thanks.
HTML Code: 
12	6	1	0	2	8
1	9	21	23	17	16
28	35	35	33	26	22
29	28	22	24	22	17
6	4	2	12	26	21
2	5	1	0	4	4
0	1	9	15	24	3
1	12	18	17	22	0
5	18	21	19	23	14
5	10	11	7	7	11
1	6	20	20	19	9
22	19	1	2	3	7
28	29	27	19	5	8
2	4	9	2	16	0
6	4	14	17	10	6
6	7	4	9	11	7
5	9	4	7	10	8
16	23	20	23	7	2
7	3	13	17	3	2
3	21	9	5	5	6
1	21	19	11	8	1
2	14	19	16	11	2
3	12	18	16	13	1
1	4	3	7	0	0
2	4	8	12	11	5
8	8	11	8	1	5
1	3	25	23	21	25
1	1	9	6	25	26
3	11	16	21	2	0
5	18	36	37	18	14
2	16	18	19	1	1
2	10	4	2	0	2
16	18	13	18	15	14
14	16	26	12	12	2
5	2	0	14	11	14
1	1	2	2	16	6
0	5	6	4	8	10
7	19	17	13	5	8
7	13	19	15	2	7
6	5	14	22	11	13
8	12	24	7	10	0
10	27	29	2	9	1
8	23	22	11	13	4




I am looking for vba code to move data from one spreadsheet tab to another. I need it to copy based on greater than or equal to and less than or equal to a certain column. I saw other related threads with just specific values; I need a range of numbers.

Attached is an example of the before with the data download, and the after when I just manually move them over to each tab. The tabs noted GT100K or GT50K means in column I values over 100,000 or 50,000, etc.

So the code would pull based on column C or C & I from the data tab, and copy to the other tabs. After moved If you could sort the tabs by B&C too, that would be perfect. If this just pulls from the top row down, then you can ignore the prior sentence since it will be sorted before running the code.

I'm not sure if my files are working so here is kind of an brief example. If you can give me the base, maybe I could manipulate the vba for my specifics.

Example :
Account Var. Amount
45000 $105
45005 $50
56100 $25
79008 $125
67002 $25

The code would move the data (first 2 rows) in one tab for accounts 45000-56009. Then move to another tab for accounts 45000-56009 and variance amounts greater than $100 (just the first row). Move data from accounts 56000-56999 to a separate tab (copy the third row into new tab). Etc.

Let me know if you have any questions.
Thanks,
Preston


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