You don’t need a loop to do this. An autofilter is much more efficient. (similar to cursor vs. where clause in SQL) Autofilter all rows that don’t contain “@” and then delete them like this: Sub KeepOnlyAtSymbolRows() Dim ws As Worksheet Dim rng As Range Dim lastRow As Long Set ws = ActiveWorkbook.Sheets(“Sheet1”) lastRow = … Read more
File -> Options -> Trust Center -> Trust Center Setttings -> Macro Settings -> Trust Access to the VBA Project object model. This is usually needed if you are referencing Extensibility library.
If all you’re trying to do is select the first blank cell in a given column, you can give this a try: Code: Public Sub SelectFirstBlankCell() Dim sourceCol As Integer, rowCount As Integer, currentRow As Integer Dim currentRowValue As String sourceCol = 6 ‘column F has a value of 6 rowCount = Cells(Rows.Count, sourceCol).End(xlUp).Row ‘for … Read more
I recorded a macro with ‘Relative References’ and this is what I got : Range(“F10”).Select ActiveCell.Offset(0, 3).Range(“A1:D11”).Select Heres what I thought : If the range selection is in quotes, VBA really wants a STRING and interprets the cells out of it so tried the following: Dim MyRange as String MyRange = “A1:D11” Range(MyRange).Select And it … Read more
Assuming that you put your files in “Files” directory relative to your master workbook your code might look like this: Sub ProcessFiles() Dim Filename, Pathname As String Dim wb As Workbook Pathname = ActiveWorkbook.Path & “\Files\” Filename = Dir(Pathname & “*.xls”) Do While Filename <> “” Set wb = Workbooks.Open(Pathname & Filename) DoWork wb wb.Close … Read more
This is what I would do safely store connection string credentials Download and install Visual Studio Express 2012 for Windows (FREE) Open it as Administrator and create a New Project. Select Visual C# then Class Library and rename it to HiddenConnectionString In the Solution Explorer, rename Class1.cs to MyServer.cs Right click your MyConnection project in … Read more
I know you have accepted a solution now that will work for you and is much simpler than the below, but if you’re interested, this would be the more direct answer to your question. You need to create a class to handle the button clicks, so every time the button is clicked it uses the … Read more
The best (and easiest) way to copy data from a workbook to another is to use the object model of Excel. Option Explicit Sub test() Dim wb As Workbook, wb2 As Workbook Dim ws As Worksheet Dim vFile As Variant ‘Set source workbook Set wb = ActiveWorkbook ‘Open the target workbook vFile = Application.GetOpenFilename(“Excel-files,*.xls”, _ … Read more
Declare them outside the subroutines, like this: Public wbA as Workbook Public wbB as Workbook Sub MySubRoutine() Set wbA = Workbooks.Open(“C:\file.xlsx”) Set wbB = Workbooks.Open(“C:\file2.xlsx”) OtherSubRoutine End Sub Sub OtherSubRoutine() MsgBox wbA.Name, vbInformation End Sub Alternately, you can pass variables between subroutines: Sub MySubRoutine() Dim wbA as Workbook Dim wbB as Workbook Set wbA = … Read more
I think this is what you want… Sub SaveWorksheetsAsCsv() Dim WS As Excel.Worksheet Dim SaveToDirectory As String Dim CurrentWorkbook As String Dim CurrentFormat As Long CurrentWorkbook = ThisWorkbook.FullName CurrentFormat = ThisWorkbook.FileFormat ‘ Store current details for the workbook SaveToDirectory = “H:\test\” For Each WS In Application.ActiveWorkbook.Worksheets WS.SaveAs SaveToDirectory & WS.Name, xlCSV Next Application.DisplayAlerts = False … Read more