MCell is an Object variable that contains a lot of informations on the cells that it represents. NewCellAddr = mCell.Offset(0, ColumnsOffset).Address 'Get the address of the cell on the column you need (Consider using a Select Case instead of ElseIf)Īnd then loop the range 'You need to replace YourSheetName with the real name of your sheetįor Each mCell In ThisWorkbook.Sheets("YourSheetName").Range(Rng).SpecialCells(xlCellTypeVisible) It is important to point out that the filter may not always be the same, so it won't always be both Column A and Column B, it might just be Column A or just Column B.ġ) To select a range: (Of course you can use a formula instead of a fixed range) Dim Rng As Range Is there a way I can fix this so that it gets the filtered cells?
Smith Male Male Female Female Male Female Female Female I filter on Jones in column A and Female in Column B to get two rows returned, rather than getting the email addresses and it will get the email addresses and because it finds the first row with the filter applied then goes to the next cell disregarding the filter.
How to make something go to next line in excel cell code#
This code checks to see if a filter has been applied and counts the amounts of rows if there is one or isn't one, it then checks to see who should be emailed (the 'Category' which is in I1 is the job position of different individuals) and then gets the email addresses of those required, the issue I'm having is say I have the following data (this is just an example of what I want to do): Column A Column B Column C Recipients = Recipients & EmailAdrs.Value & " " Set EmailAdrs = (1).SpecialCells(xlCellTypeVisible).Cells(1, CellReference).Offset(0 + Index, 0) ' CellReference is the amount of columns to the right of column A, ie Column A is 0 so CellReference below is J - which is the column location of the email address according to that category ' In Range("I1") there is the job category the user wants to email RowsCount = Application.CountA(Range("A2:A" & Rows.Count)) - 1 RowsCount = (1).SpecialCells(xlCellTypeVisible).Cells.Count - 1ĮlseIf ActiveSheet.FilterMode = False Then Set objOutlook = CreateObject("Outlook.Application") I am currently using the following code: Sub SendEmail()
0 kommentar(er)
